4

I was wondering if I could hardcode an array or "range" into a formula. So, for example, if I wanted to see if a month in B2 is March, April, June or July, I would like to compress this this:

=COUNTIF(a1:a4,MONTH(B2))>0

where A1:a4 = 3,4,6,7

to simply:

=COUNTIF((3,4,6,7),MONTH(B2))>0

so that I do not need extraneous lists in other cells

Sled
  • 18,541
  • 27
  • 119
  • 168
cperlmutter
  • 308
  • 3
  • 9

2 Answers2

4

Yes you can, just use "{ }" rather than "( )" to surround your list of values.

=COUNTIF({3,4,6,7},MONTH(B2))>0

I didn't test if your logic worked but showed where to put the braces.

Steven
  • 422
  • 1
  • 4
  • 12
  • 2
    This seems to work for hardcoded numbers, which technically answers the question correctly, but not for hybrid constants/cell references... e.g.`=XIRR({-100,105},{42799,43164})` works but `=XIRR({-B6,105},{42799,43164})` doesn't – cperlmutter Jul 11 '17 at 18:50
  • unfortunately it doesn't work on a European system where the separator is a semicolon. I tried it with semicolons inside the brackets, and all kind of brackets and parentheses, but couldn't make it work – robotik Aug 16 '22 at 12:21
0

Use the SUMPRODUCT() formula:

=SUMPRODUCT(({3,4,6,7}=MONTH(B2))*1)

This will not work as a conditional formatting formula, only as a worksheet formula.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81