Re: How to calculate the number of non-zero cells in range?

[Claudia d'Amato]

Your solution is NOT working, if a cell contains a formula!

So if a cell contains e.g.=SUM(A5:A15) then is cell is non-blank and
counts as 1. But this is not intended.

If the formula evaluates to 0 then this cell should NOT count.
I need a solution which evaluates any possibly existing formulas at
first and then checks if the cell is non-blank/non-zero.

Any other solutions than COUNTA() for this task?

Claudia

 

[ryguy7272]

This function will count non-blanks:
=SUMPRODUCT(--(A1:A20<>""))
Note, Enter as Ctrl + Shift + Enter, not just Enter

This will count blanks:
=COUNTIF(A1:A20,"")
Normal Enter

This will count values >0:
=SUMPRODUCT(--(A1:A20>0))
Note, Enter as Ctrl + Shift + Enter, not just Enter

Just for fun...this will count zeros:
=COUNTIF(A1:A20,0)
Normal Enter




HTH,
Ryan---

 

[Rick Rothstein]

Maybe this array-entered** formula....

=INDEX(B1:B25,MAX(ROW(B12:B25)*(B12:B25<>"")*(B12:B25<>0)))

**commit formula using Ctrl+Shift+Enter, not just Enter by itself

 

[Rick Rothstein]

Hello, I've just joined, so this is my first post. I'm trying

OH......MY.......GOSH!!!!! That's fantastic! Thanks ever so
much. Is there any chance of getting a brief explanation of
what's going on there? I've limited knowledge of Excel
functions, but always willing to know more.

Perhaps this link will help...

http://www.cpearson.com/Excel/ArrayFormulas.aspx

 

[Bernd P]

Maybe this array-entered** formula....

=INDEX(B1:B25,MAX(ROW(B12:B25)*(B12:B25<>"")*(B12:B25<>0)))

**commit formula using Ctrl+Shift+Enter, not just Enter by itself

Hello,

Why not just
=LOOKUP(2,1/(B1:B25<>""),B1:B25)
?

(non-array formula)

Regards,
Bernd

 

[Rick Rothstein]

Maybe this array-entered** formula....

Hello,

Why not just
=LOOKUP(2,1/(B1:B25<>""),B1:B25)
?

(non-array formula)

Probably because I answered that post at 4:15 in the morning (just before I
was going to sleep).<g>

Thanks for following up on this.