Counting Inbetween Spaces

P

Peter

If I have a column that likes like this:

5
5
6
2 4

2
3
2 3

1 1

2
3
1 3


I want to count each the number of items in each group and have it
show up next to it's group as in the example. I assume it's some kind
of array formula similar to summing the numbers in each group.

Thanks very much for all of the help. I really do appreciate it.
This is great group.
 
J

JMB

one way you could try, assuming the data begins in cell A1. if not, change
the cell references:

=IF(A2="",SUMPRODUCT(--(ROW(A$1:A1)>=MAX((A$1:A1="")*(ROW(A$1:A1)),1)),A$1:A1),"")


entered normally
 
T

T. Valko

One way...

This requires that the cell immediately above the data is empty and the next
cell after the last entry is empty. So,assuming your data starts in cell A2
with cell A1 being empty.

Enter this formula in B2 and copy down as needed:

=IF(A2="","",IF(A3="",COUNT(A2:INDEX(A$1:A1,LOOKUP(2,1/(A$1:A1=""),ROW(A$1:A1)))),""))
 
T

T. Valko

Improvement...

This formula doesn't require the cell immediately above the data be empty.

Assuming data starts in A2.

=IF(A3="",COUNT(A2:INDEX(A$2:A2,MAX(INDEX((A$2:A2="")*ROW(A$2:A2),,1)))),"")
 
P

Peter

Hi and thanks for the formula. I tried dragging it down after
changing the references, but after the first group of numbers it
doesn't correctly count from then on. Pilot error?
 
G

g-

If your data starts in A2 then put this formula in B2:

=IF(A3="",COUNT(A$2:A2)-SUM(B1:B$2),"")

Copy this formula down and you'll get what you need.

It's also a little easier to understand than what has been posted
previously.

g-
[email protected]
___________________________________

Need holiday money. Got any jobs, big or small, drop me a line! Thanks!
 
P

Peter

Hi, it works for me to if I insert it into a blank page and start
fresh. In my spreadsheet, no.

There is not more than one empty cell between any group. So far I
only have three groups. The first one has 14 entries and the next two
each have 27, but it says there are 19 for the last two groups.

I'm assuming it doesn't make any difference how many columns there are
between the data and the count, but in case it did make a difference
I just counted a column that was adjacent.

Very perplexing.
 
T

T. Valko

If your data starts in A2 then put this formula in B2:
=IF(A3="",COUNT(A$2:A2)-SUM(B1:B$2),"")
Click to expand...

That works just fine as long as the data doesn't start in row 1.
 
Top