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.

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.
>

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" <(E-Mail Removed)> wrote in message
news(E-Mail Removed)...
> 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.

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news(E-Mail Removed)...
>> 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.
>
>

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"
<(E-Mail Removed)> 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)))),"")

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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"
> <(E-Mail Removed)> 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)))),"")
>

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-
(E-Mail Removed)
___________________________________

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





"Peter" <(E-Mail Removed)> wrote in message
news(E-Mail Removed)...
> 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.

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"
<(E-Mail Removed)> 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?

>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-" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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-
> (E-Mail Removed)
> ___________________________________
>
> Need holiday money. Got any jobs, big or small, drop me a line! Thanks!
>
>
>
>
>
> "Peter" <(E-Mail Removed)> wrote in message
> news(E-Mail Removed)...
>> 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.
>
>

See g-'s reply.

Nice and simple!

--
Biff
Microsoft Excel MVP


"Peter" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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"
> <(E-Mail Removed)> 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?
>