CODEWITHSUNDEEP
excelarray-formulas
fuadj
fuadj

Reputation: 454

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)

Upvotes: 0

Views: 30

Answers (1)

barry houdini
barry houdini

Reputation: 46331

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

Upvotes: 2

Related Questions