Excel COUNT () vs. COUNTIF () with arrays
I think this should be a simple question, but for some reason I can't seem to find a solution anywhere.
I have a long formula in excel that ultimately returns an array of four elements - i.e. {1,2,0,0}. I want to count how many of the received numbers are greater than zero.
When I use =COUNT({1,2,0,0})
the result for this, I get the expected response of 4. But when I try to use =COUNTIF({1,2,0,0}, ">0")
, a message appears that my formula has problems.
Is there something I am doing wrong? Is there an array equivalent for COUNTIF ()?
source to share
The function seems to COUNTIF
only work on ranges, while the function COUNT
can use an array.
Give it a SUMPRODUCT
try. Below is a small extended form of your example that I used to validate the formula. It basically checks if each value in the array is greater than 0, and if it does, it assigns it a value of 1. Then it SUMPRODUCT
goes through and adds 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))
source to share
Probably the shortest way to achieve this is to simply convert the value, TRUE
or FALSE
returned from the validation test, to a number using a function INT
. TRUE
matches 1
, but FALSE
matches 0
. Then SUM
those are 1 and 0.
=SUM(INT({1,2,0,0}>0))
Or, as Barry Houdini points out, you can coerce a boolean to an int with:
=SUM(({1,2,0,0}>0)*1)
Or:
=SUM(({1,2,0,0}>0)+0)
source to share