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

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

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

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

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

Hello, I've just joined, so this is my first post. I'm trying to return
the contents of the last non-zero/non-blank cell in a column...this
returns the first,=INDEX(B12:B23,MATCH(TRUE,B12:B23<0,0)) (array
function), can someone help me to find the function to return the last
cell please? I have looked, but all my attempts have failed, like this
one...=INDEX(B12:B23,(MAX(INDEX((B12:B23<0)*ROW(B 12:B23),1,0))-ROW(B12:B23)+1))
(non array).

Thanks in advance

LewBoy


--
LewBoy
------------------------------------------------------------------------
LewBoy's Profile: http://www.thecodecage.com/forumz/member.php?userid=801
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=122328

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

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 (MVP - Excel)


"LewBoy" wrote in message
...

Hello, I've just joined, so this is my first post. I'm trying to return
the contents of the last non-zero/non-blank cell in a column...this
returns the first,=INDEX(B12:B23,MATCH(TRUE,B12:B23<0,0)) (array
function), can someone help me to find the function to return the last
cell please? I have looked, but all my attempts have failed, like this
one...=INDEX(B12:B23,(MAX(INDEX((B12:B23<0)*ROW(B 12:B23),1,0))-ROW(B12:B23)+1))
(non array).

Thanks in advance

LewBoy


--
LewBoy
------------------------------------------------------------------------
LewBoy's Profile: http://www.thecodecage.com/forumz/member.php?userid=801
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=122328

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

Rick Rothstein;479208 Wrote:
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 (MVP - Excel)


"LewBoy" wrote in message
...

Hello, I've just joined, so this is my first post. I'm trying to
return
the contents of the last non-zero/non-blank cell in a column...this
returns the first,=INDEX(B12:B23,MATCH(TRUE,B12:B23<0,0)) (array
function), can someone help me to find the function to return the
last
cell please? I have looked, but all my attempts have failed, like
this

one...=INDEX(B12:B23,(MAX(INDEX((B12:B23<0)*ROW(B 12:B23),1,0))-ROW(B12:B23)+1))
(non array).

Thanks in advance

LewBoy


--
LewBoy

------------------------------------------------------------------------
LewBoy's Profile: 'The Code Cage Forums - View Profile: LewBoy'
(http://www.thecodecage.com/forumz/member.php?userid=801)
View this thread:
' How to calculate the number of non-zero cells in range? - The
Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=122328)


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.
Thanks again.
LewBoy


--
LewBoy
------------------------------------------------------------------------
LewBoy's Profile: http://www.thecodecage.com/forumz/member.php?userid=801
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=122328

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

Hello, I've just joined, so this is my first post. I'm trying
to return the contents of the last non-zero/non-blank
cell in a column...this returns the first,
=INDEX(B12:B23,MATCH(TRUE,B12:B23<0,0))
(array function), can someone help me to find the
function to return the last cell please? I have looked,
but all my attempts have failed, like this one
=INDEX(B12:B23,(MAX(INDEX((B12:B23<0)*ROW(B12:B23 ),1,0))-ROW(B12:B23)+1))
(non array).

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

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

--
Rick (MVP - Excel)

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

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

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

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)

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.

--
Rick (MVP - Excel)