Counting With Blank Rows

[SMDIYDLI]

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.

 

[Guest]

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]

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.

Thanks again for any response.

 

[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

 

[SMDIYDLI]

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.