Counting Inbetween Spaces
 

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.

Counting Inbetween Spaces
 

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


"Peter" wrote:

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.

Counting Inbetween Spaces
 

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)))),""))

--
Biff
Microsoft Excel MVP


"Peter" wrote in message
...
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.

Counting Inbetween Spaces
 

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)))),"")

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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)))),""))

--
Biff
Microsoft Excel MVP


"Peter" wrote in message
...
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.

Counting Inbetween Spaces
 

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?

On Fri, 14 Dec 2007 00:05:06 -0500, "T. Valko"
wrote:

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)))),"")

Counting Inbetween Spaces
 

Works for me. See this screencap:

http://img205.imageshack.us/img205/3...tconsecpt0.jpg

after changing the references

If it doesn't work tell me where the data is. Is there more than one empty
cell between any group?

--
Biff
Microsoft Excel MVP


"Peter" wrote in message
...
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?

On Fri, 14 Dec 2007 00:05:06 -0500, "T. Valko"
wrote:

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:A 2="")*ROW(A$2:A2),,1)))),"")

Counting Inbetween Spaces
 

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-

___________________________________

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





"Peter" wrote in message
...
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.

Counting Inbetween Spaces
 

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.

On Fri, 14 Dec 2007 01:09:01 -0500, "T. Valko"
wrote:

Works for me. See this screencap:

http://img205.imageshack.us/img205/3...tconsecpt0.jpg

after changing the references

If it doesn't work tell me where the data is. Is there more than one empty
cell between any group?

Counting Inbetween Spaces
 

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

Yeah, you got that right!

Nice and simple. I'm calling it a day!

--
Biff
Microsoft Excel MVP


"g-" wrote in message
...
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-

___________________________________

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





"Peter" wrote in message
...
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.

Counting Inbetween Spaces
 

See g-'s reply.

Nice and simple!

--
Biff
Microsoft Excel MVP


"Peter" wrote in message
...
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.

On Fri, 14 Dec 2007 01:09:01 -0500, "T. Valko"
wrote:

Works for me. See this screencap:

http://img205.imageshack.us/img205/3...tconsecpt0.jpg

after changing the references

If it doesn't work tell me where the data is. Is there more than one empty
cell between any group?

Counting Inbetween Spaces
 

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

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

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


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
It's also a little easier to understand than what has been posted
previously.

Yeah, you got that right!

Nice and simple. I'm calling it a day!

--
Biff
Microsoft Excel MVP


"g-" wrote in message
...
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-

___________________________________

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





"Peter" wrote in message
...
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.

Counting Inbetween Spaces
 

That worked. Thanks guys.

On Fri, 14 Dec 2007 02:45:23 -0500, "T. Valko"
wrote:

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

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

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