CODEWITHSUNDEEP
arraysexcel
HAWK
HAWK

Reputation: 33

Excel COUNT() vs COUNTIF() with arrays

I think this should be a straightforward question, but for some reason I can't find a solution anywhere.

I have a lengthy formula in excel that, ultimately, returns an array of four items -- i.e. {1,2,0,0}. I want to count how many of the resulting numbers are greater than zero.

When I use =COUNT({1,2,0,0}) on this result, I get the expected answer of 4. But when I try to use =COUNTIF({1,2,0,0}, ">0") it pops up with an error saying that my formula has a problem.

Is there something I'm doing wrong? Is there an array equivalent for COUNTIF() ?

Upvotes: 3

Views: 569

Answers (2)

Mark Balhoff
Mark Balhoff

Reputation: 2356

Probably the most concise way to accomplish this is to just convert the TRUE or FALSE value returned from the validation check into a number with the INT function. TRUE translates to 1 and FALSE translates to 0. Then SUM those 1's and 0's.

=SUM(INT({1,2,0,0}>0))

Or as Barry Houdini points out, you can coerce the boolean to an int with:

=SUM(({1,2,0,0}>0)*1)

Or:

=SUM(({1,2,0,0}>0)+0)

Upvotes: 0

tittaenälg
tittaenälg

Reputation: 386

It appears the COUNTIF function only works on ranges, while the COUNT function can utilize an array.

Give SUMPRODUCT a try. Below is a slightly expanded form of your example which I used to test the formula. It basically checks to see if each value in the array is greater than 0, and if it is, it assigns it a value of 1. Then SUMPRODUCT goes through and adds up all the 1s to give you the total number of values greater than 0.

=SUMPRODUCT(IF({1,0,3,0,5,0,0,6,9,9,0,7,0}>0,1,0))

Upvotes: 3

Related Questions