Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate the position of a formula and number of cells from it | Excel Worksheet Functions | |||
How to calculate the number of non-zero cells in range? | Excel Discussion (Misc queries) | |||
Calculate number of weeks between two cells | Excel Discussion (Misc queries) | |||
Calculate number between two alphanumeric cells | Excel Discussion (Misc queries) | |||
how to calculate the number of non blank cells from any column? | Excel Worksheet Functions |