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

  • Thread starter Thread starter Claudia d'Amato
  • Start date Start date
C

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
 
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---
 
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
 
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
Click to expand...
Hello,

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

(non-array formula)

Regards,
Bernd
 
Maybe this array-entered** formula....
Hello,

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

(non-array formula)
Click to expand...

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.
 
Back
Top