How to count only TRUE values in an array?

[Rnkhch]

Hello,

I know how to do this using COUNTIF (or COUNTIFS) which is super easy, but I'm intentionally trying to do it the hard way to improve my Excel array handling skills . I'm trying to use the COUNT function in conjunction with the IF function in the array format to count only the number of TRUE values, in this case the number of cells that have values greater than 10, so I'm trying to write something like this:

{=IF((A1:D1>10),(COUNT(A1:D1)))}

But I can't get it to work, (and I see from the Evaluate Formula dialog that there is no one-to-one correlation between the IF test and the Count, so the COUNT portion of the formula just does its counting to "4" and disregards the IF condition ).

Thanks for any suggestions!

 

[Ron Coderre]

Using your posted example...

Your formula uses the COUNT(A1:D1) section for every value greater than 10 and resolves to this:
{4,FALSE,4,4}


Also, since the COUNT function counts numeric values and skips text and errors, you'd need an array that returns text or errors for the values you want to skip.

This ARRAY FORMULA does what you want:
=COUNT(1/(A1:D1>10))

However, you can avoid CTRL+SHIFT+ENTER if you use the INDEX in this kind of structure:
=COUNT(INDEX(1/(A1:D1>10),0))

Both formulas return 3.

Does that help?

 

[DanteAmor]

Another way with a "regular" formula that considers an array of data
=SUMPRODUCTO((A1:D1>10)+0)

Or with an array formula (To accepte press Ctrl+Shift+Enter.):
=SUM(IF(A1:D1>10,1))

 

[Rnkhch]

Fantastic guys! Thanks a lot!!!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.