Excel formula: counting array elements with a criteria

2
votes

I have an array, which is the result of the multiplication of two COUNTIF functions.

The result is {0,2,7,4,0}. I want to count the number of non-zero elements. I tried the following:

=COUNTIF(COUNTIF*COUNTIF,">0") <- here the two inner COUNTIFs are short for the complete formula

It did not work. I then tried the following, which did not work either.

=COUNTIF({0,2,7,4,0},">0")

I resorted to using the following array formula and it worked.

{=SUM(IF(COUNTIF*COUNTIF>0,1,0))}

Two questions:

  1. does COUNTIF only accept cell reference but not constant array?
  2. any solution other than array formula?

Thanks.

1
arraysexcelexcel-formula
Doesn't =sumproduct(--((COUNTIF*COUNTIF)>0)) work? sumproduct provides one level of the cyclic calculation of an array formula. – user4039065
Thanks. It works. – joehua

1 Answers

0
votes

  1. The first argument of COUNTIF must be a range, nothing else will do.

  2. You can just enter this as an ordinary formula:

    =SUM(--({0,2,7,4,0}>0))

  3. However when the array comes from a calculation, you need to coerce it to process all the elements either

    =SUM(INDEX(--(A1:A5>0),0))

or like @Jeeped with Sumproduct

=SUMPRODUCT(--(A1:A5>0))