Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
counting nonblank rows
I'm trying to figure out how to integrate the number of nonblank rows within
a given array into my formula. I have tried COUNTA(ROWS(C6:Z29),1), but that does not give the correct result. Any help that you all may offer would be greatly appreciated. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
counting nonblank rows
Try this:
=Counta(C6:Z29) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "stumped" wrote in message ... I'm trying to figure out how to integrate the number of nonblank rows within a given array into my formula. I have tried COUNTA(ROWS(C6:Z29),1), but that does not give the correct result. Any help that you all may offer would be greatly appreciated. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
counting nonblank rows
Thanks for the quick response. I tried that but it still is not working
properly. Would it matter that some of these cells contain formulas which may or may not prompt a value? "RagDyer" wrote: Try this: =Counta(C6:Z29) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "stumped" wrote in message ... I'm trying to figure out how to integrate the number of nonblank rows within a given array into my formula. I have tried COUNTA(ROWS(C6:Z29),1), but that does not give the correct result. Any help that you all may offer would be greatly appreciated. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
counting nonblank rows
Hi,
RagDyer's reply counts the number of cells not the number of rows. Don't know how you would use it in a formula but I think that you will need to use a column to count the number of blank cells in each row and then count the number of cells in the column < 0. Example say in column AA:- =COUNTA(C6:Z6) =COUNTA(C7:Z7) continue to row 29 Then in cell AA30 insert:- =COUNTIF(AA6:AA29,"0") -- Regards, OssieMac "stumped" wrote: Thanks for the quick response. I tried that but it still is not working properly. Would it matter that some of these cells contain formulas which may or may not prompt a value? "RagDyer" wrote: Try this: =Counta(C6:Z29) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "stumped" wrote in message ... I'm trying to figure out how to integrate the number of nonblank rows within a given array into my formula. I have tried COUNTA(ROWS(C6:Z29),1), but that does not give the correct result. Any help that you all may offer would be greatly appreciated. |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
counting nonblank rows
Try this array formula** :
=SUMPRODUCT(--(MMULT(--(C6:Z29<""),TRANSPOSE(COLUMN(C6:Z29))^0)0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "stumped" wrote in message ... I'm trying to figure out how to integrate the number of nonblank rows within a given array into my formula. I have tried COUNTA(ROWS(C6:Z29),1), but that does not give the correct result. Any help that you all may offer would be greatly appreciated. |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
counting nonblank rows
Since the formula has to be array entered you can save a few keystokes and
just use SUM: =SUM(--(MMULT(--(C6:Z29<""),TRANSPOSE(COLUMN(C6:Z29))^0)0)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this array formula** : =SUMPRODUCT(--(MMULT(--(C6:Z29<""),TRANSPOSE(COLUMN(C6:Z29))^0)0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "stumped" wrote in message ... I'm trying to figure out how to integrate the number of nonblank rows within a given array into my formula. I have tried COUNTA(ROWS(C6:Z29),1), but that does not give the correct result. Any help that you all may offer would be greatly appreciated. |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
counting nonblank rows
Thanks T,
Your suggestion seems to come close, but I keep coming up with a value of 15 when it should be 12. I was trying to follow the logic of your formula, but I don't know that I understand arrays really well or the exponent of 0. Could you explain a little further? Thanks "T. Valko" wrote: Since the formula has to be array entered you can save a few keystokes and just use SUM: =SUM(--(MMULT(--(C6:Z29<""),TRANSPOSE(COLUMN(C6:Z29))^0)0)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this array formula** : =SUMPRODUCT(--(MMULT(--(C6:Z29<""),TRANSPOSE(COLUMN(C6:Z29))^0)0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "stumped" wrote in message ... I'm trying to figure out how to integrate the number of nonblank rows within a given array into my formula. I have tried COUNTA(ROWS(C6:Z29),1), but that does not give the correct result. Any help that you all may offer would be greatly appreciated. |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
counting nonblank rows
Would it matter that some of these cells contain formulas
which may or may not prompt a value? In other words, you have formulas that return formula blanks? If an entire row contains formula blanks do you want that row counted? -- Biff Microsoft Excel MVP "stumped" wrote in message ... Thanks T, Your suggestion seems to come close, but I keep coming up with a value of 15 when it should be 12. I was trying to follow the logic of your formula, but I don't know that I understand arrays really well or the exponent of 0. Could you explain a little further? Thanks "T. Valko" wrote: Since the formula has to be array entered you can save a few keystokes and just use SUM: =SUM(--(MMULT(--(C6:Z29<""),TRANSPOSE(COLUMN(C6:Z29))^0)0)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this array formula** : =SUMPRODUCT(--(MMULT(--(C6:Z29<""),TRANSPOSE(COLUMN(C6:Z29))^0)0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "stumped" wrote in message ... I'm trying to figure out how to integrate the number of nonblank rows within a given array into my formula. I have tried COUNTA(ROWS(C6:Z29),1), but that does not give the correct result. Any help that you all may offer would be greatly appreciated. |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
counting nonblank rows
Or, do you only want to count those rows where *every* cell is not not
blank? -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Would it matter that some of these cells contain formulas which may or may not prompt a value? In other words, you have formulas that return formula blanks? If an entire row contains formula blanks do you want that row counted? -- Biff Microsoft Excel MVP "stumped" wrote in message ... Thanks T, Your suggestion seems to come close, but I keep coming up with a value of 15 when it should be 12. I was trying to follow the logic of your formula, but I don't know that I understand arrays really well or the exponent of 0. Could you explain a little further? Thanks "T. Valko" wrote: Since the formula has to be array entered you can save a few keystokes and just use SUM: =SUM(--(MMULT(--(C6:Z29<""),TRANSPOSE(COLUMN(C6:Z29))^0)0)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this array formula** : =SUMPRODUCT(--(MMULT(--(C6:Z29<""),TRANSPOSE(COLUMN(C6:Z29))^0)0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "stumped" wrote in message ... I'm trying to figure out how to integrate the number of nonblank rows within a given array into my formula. I have tried COUNTA(ROWS(C6:Z29),1), but that does not give the correct result. Any help that you all may offer would be greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Nonblank cells based on criteria in another cell | Excel Discussion (Misc queries) | |||
How do I count nonblank cells in rows within Excel? | New Users to Excel | |||
Fill down to nonblank rows | Excel Worksheet Functions | |||
counting nonblank cells | Excel Worksheet Functions | |||
Counting nonblank and non white space cells | Excel Worksheet Functions |