Search code examples
excelarray-formulas

Excel conditional array duplicating result

I'm trying to understand why 

{=COUNT(IF({TRUE, FALSE, TRUE}, {3,4,5}))}

returns 2 (which is the expected result) but the following returns 6

{=COUNT(IF({TRUE,FALSE,TRUE}, A1:A3))}

Here is a snapshot of the result enter image description here

The formula evaluation shows what is happening enter image description here

NOTE: This is a simplified version of a problem I am facing(i.e: it isn't just a curiosity question)

Solution

  • The two formulas are not equivalent, your second formula is equivalent to this:

    =COUNT(IF({TRUE, FALSE, TRUE}, {3;4;5}))

    Note: {3;4;5} not {3,4,5}

    That will also return 6. As you can see from the evaluation it returns a 3x3 matrix with 6 numbers hence that result

    If you want the result to be 2 you need to transpose A1:A3 like this

    =COUNT(IF({TRUE,FALSE,TRUE}, TRANSPOSE(A1:A3)))

    .....or use a row of values like B2:D2 in place of A1:A3