I would like to count the number of non-zero (=non-blank) cells in a range (say D9:D30).

How can I do this with an Excel formula ?

Claudia

COUNTA will count all non-blank cells............text or numeric.

COUNT will count all numeric cells.


Gord Dibben MS Excel MVP

On 28 Jul 2009 21:30:25 GMT, (Claudia d'Amato)
wrote:

I would like to count the number of non-zero (=non-blank) cells in a range (say D9:D30).

How can I do this with an Excel formula ?

Claudia

On 28 Jul 2009 21:30:25 GMT, (Claudia d'Amato)
wrote:

I would like to count the number of non-zero (=non-blank) cells in a range (say D9:D30).

How can I do this with an Excel formula ?

Claudia


Take a look at the worksheet function COUNTA()

Hope this helps / Lars-Åke

Do any of the cells contain formula blanks?

Try one of these (accounts for formula blanks):

=SUMPRODUCT(--(LEN(D9:D30)0))

=ROWS(D9:D30)-COUNTBLANK(D9:D30)

--
Biff
Microsoft Excel MVP


"Claudia d'Amato" wrote in message
...
I would like to count the number of non-zero (=non-blank) cells in a range
(say D9:D30).

How can I do this with an Excel formula ?

Claudia

Here is one that will account for either blanks or zeros. I wasn't
clear from the original question if we need to exclude both.

=SUMPRODUCT((NOT(ISBLANK(D9:D30))*1)*(NOT(D9:D30=0 )*1))

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

On Tue, 28 Jul 2009 14:35:59 -0700, Gord Dibben <gorddibbATshawDOTca
wrote:

COUNTA will count all non-blank cells............text or numeric.

COUNT will count all numeric cells.


Gord Dibben MS Excel MVP

On 28 Jul 2009 21:30:25 GMT, (Claudia d'Amato)
wrote:

I would like to count the number of non-zero (=non-blank) cells in a range (say D9:D30).

How can I do this with an Excel formula ?

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:A200))
Note, Enter as Ctrl + Shift + Enter, not just Enter

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




HTH,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Claudia d'Amato" wrote:

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

On Tue, 28 Jul 2009 14:35:59 -0700, Gord Dibben <gorddibbATshawDOTca
wrote:

COUNTA will count all non-blank cells............text or numeric.

COUNT will count all numeric cells.


Gord Dibben MS Excel MVP

On 28 Jul 2009 21:30:25 GMT, (Claudia d'Amato)
wrote:

I would like to count the number of non-zero (=non-blank) cells in a range (say D9:D30).

How can I do this with an Excel formula ?

Claudia