Counting With Blank Rows
Hi,
If I have a column of numbers like this: 4 5 6 3 7 8 9 3 2 7 4 1 4 How do I count the number of items between the blanks so it will look like I have it in the example. The number to the right and bottom is the count. So, for the numbers 4,5 6 the count is three. For 7,8 9 the count is three and for the numbers 2,7,4,1 the count is four. Is there a way to do this withoug using a macro? Thanks. |
Hi,
You need to use the "count" function. If the rows in your example are 1-12 and the column is A, for the first group you'd type in (in column B) "=count(a1:a3)" (without the quote marks), for the second you'd type in "=count(a5:a7)" and in the last you'd type in "=count(a9:a12)". Note that after you've typed in the first "(" you can click and drag your mouse over the cells you want to count up and excel will automatically fill in the blanks for you. Excel has a pretty good help menu for the functions. It may be worth your while taking some time to have a look through it for the more used functions such as sum, count etc. Remember, count will only work for numbers not on other characters or text, you can count them using =counta(). Count will also ignore blank cells. Fiona "SMDIYDLI" wrote: Hi, If I have a column of numbers like this: 4 5 6 3 7 8 9 3 2 7 4 1 4 How do I count the number of items between the blanks so it will look like I have it in the example. The number to the right and bottom is the count. So, for the numbers 4,5 6 the count is three. For 7,8 9 the count is three and for the numbers 2,7,4,1 the count is four. Is there a way to do this withoug using a macro? Thanks. |
Hi,
You need to use the "count" function. If the rows in your example are 1-12 and the column is A, for the first group you'd type in (in column B) "=count(a1:a3)" (without the quote marks), for the second you'd type in "=count(a5:a7)" and in the last you'd type in "=count(a9:a12)". Note that after you've typed in the first "(" you can click and drag your mouse over the cells you want to count up and excel will automatically fill in the blanks for you. Thanks for the response. I am quite familiar with the count function, but this question is a little more complicated than that. I want one formula I can drag down through the spread sheet that will automatically count the number of items for each day. Or in the case of my spreadsheet, the number of items between blank rows. So for example I might have five items the first day three the second and 48 on the third. Next to the last item of the day I want to place the count like the 5 in parenthesis below. 3 7 8 2 6 (5) The count would be five. The next day I input new data and I would like to see in an instant the count for that day too. Sometimes the list is quite long and I don't want to count by hand. So every day when I'm through imputing data the spreadsheet automatically counts the number of entries as in my original example, which is below. If I have a column of numbers like this: 4 5 6 3 7 8 9 3 2 7 4 1 4 How do I count the number of items between the blanks so it will look like I have it in the example. The number to the right and bottom is the count. So, for the numbers 4,5 6 the count is three. For 7,8 9 the count is three and for the numbers 2,7,4,1 the count is four. Is there a way to do this withoug using a macro? Thanks again for any response. |
Hi
in B1 enter the following array formula (entered with CTRL+SHIFT+ENTER) formula: =IF(A2<"","",COUNT(OFFSET($A2,0,0,-(SUM(ROW($A2))-MAX(IF($A$1:$A1="",ROW($A$1:$A1),0)))))) and copy this down -- Regards Frank Kabel Frankfurt, Germany SMDIYDLI wrote: Hi, If I have a column of numbers like this: 4 5 6 3 7 8 9 3 2 7 4 1 4 How do I count the number of items between the blanks so it will look like I have it in the example. The number to the right and bottom is the count. So, for the numbers 4,5 6 the count is three. For 7,8 9 the count is three and for the numbers 2,7,4,1 the count is four. Is there a way to do this withoug using a macro? Thanks. |
From: "Frank Kabel"
Hi in B1 enter the following array formula (entered with CTRL+SHIFT+ENTER) formula: =IF(A2<"","",COUNT(OFFSET($A2,0,0,-(SUM(ROW($A2))-MAX(IF($A$1:$A1="",ROW ($A$1:$A1),0)))))) and copy this down Beautiful. Thanks, so much. |
All times are GMT +1. The time now is 12:24 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com