Dear Expert,
Would like to count the frequency each time it shows up .
Below is the example ....

4 shows up 3 times ...
5 shows up 1 time
......... etc etc 7 shows up 3 times, 1 shows up 2 times at the bottom....
Is there any function that can capture this coutning on each movement?

Numbers Times
4
4
4 3
5 1
4
4 2
5
5 2
4
4
4 3
3 1
6
6
6 3
7
7
7 3
1
1 2

Hi

With numbers in A2 and down, insert this formula in B2 and copy it down as
required:

=IF(A2<>A3,COUNTIF($A$2:A2,A2)-SUMPRODUCT(--($A1:A$2=A2),$B1:B$2),"")

Regards,
Per

"Elton Law" <(E-Mail Removed)> skrev i meddelelsen
news:A17E82B9-2953-40A8-BA9F-(E-Mail Removed)...
> Dear Expert,
> Would like to count the frequency each time it shows up .
> Below is the example ....
>
> 4 shows up 3 times ...
> 5 shows up 1 time
> ........ etc etc 7 shows up 3 times, 1 shows up 2 times at the bottom....
> Is there any function that can capture this coutning on each movement?
>
> Numbers Times
> 4
> 4
> 4 3
> 5 1
> 4
> 4 2
> 5
> 5 2
> 4
> 4
> 4 3
> 3 1
> 6
> 6
> 6 3
> 7
> 7
> 7 3
> 1
> 1 2
>

Use the Subtotal feature.
http://www.ozgrid.com/Excel/subtotal.htm



--
Regards
Dave Hawley
www.ozgrid.com
"Elton Law" <(E-Mail Removed)> wrote in message
news:A17E82B9-2953-40A8-BA9F-(E-Mail Removed)...
> Dear Expert,
> Would like to count the frequency each time it shows up .
> Below is the example ....
>
> 4 shows up 3 times ...
> 5 shows up 1 time
> ........ etc etc 7 shows up 3 times, 1 shows up 2 times at the bottom....
> Is there any function that can capture this coutning on each movement?
>
> Numbers Times
> 4
> 4
> 4 3
> 5 1
> 4
> 4 2
> 5
> 5 2
> 4
> 4
> 4 3
> 3 1
> 6
> 6
> 6 3
> 7
> 7
> 7 3
> 1
> 1 2
>

Thanks for reply.
But subtotal does not suit my need as I don't want to change the display of
the spreadsheet. I take a tiny portion out of the spreadsheet only. Thanks

"ozgrid.com" wrote:

> Use the Subtotal feature.
> http://www.ozgrid.com/Excel/subtotal.htm
>
>
>
> --
> Regards
> Dave Hawley
> www.ozgrid.com
> "Elton Law" <(E-Mail Removed)> wrote in message
> news:A17E82B9-2953-40A8-BA9F-(E-Mail Removed)...
> > Dear Expert,
> > Would like to count the frequency each time it shows up .
> > Below is the example ....
> >
> > 4 shows up 3 times ...
> > 5 shows up 1 time
> > ........ etc etc 7 shows up 3 times, 1 shows up 2 times at the bottom....
> > Is there any function that can capture this coutning on each movement?
> >
> > Numbers Times
> > 4
> > 4
> > 4 3
> > 5 1
> > 4
> > 4 2
> > 5
> > 5 2
> > 4
> > 4
> > 4 3
> > 3 1
> > 6
> > 6
> > 6 3
> > 7
> > 7
> > 7 3
> > 1
> > 1 2
> >
>

In B2 and copied down;

=IF(A2<>A3,SUMIF(A1:A2000,A2),"")



--
Regards
Dave Hawley
www.ozgrid.com
"Elton Law" <(E-Mail Removed)> wrote in message
news:1FA2CDDE-707B-4F83-8797-(E-Mail Removed)...
> Thanks for reply.
> But subtotal does not suit my need as I don't want to change the display
> of
> the spreadsheet. I take a tiny portion out of the spreadsheet only. Thanks
>
> "ozgrid.com" wrote:
>
>> Use the Subtotal feature.
>> http://www.ozgrid.com/Excel/subtotal.htm
>>
>>
>>
>> --
>> Regards
>> Dave Hawley
>> www.ozgrid.com
>> "Elton Law" <(E-Mail Removed)> wrote in message
>> news:A17E82B9-2953-40A8-BA9F-(E-Mail Removed)...
>> > Dear Expert,
>> > Would like to count the frequency each time it shows up .
>> > Below is the example ....
>> >
>> > 4 shows up 3 times ...
>> > 5 shows up 1 time
>> > ........ etc etc 7 shows up 3 times, 1 shows up 2 times at the
>> > bottom....
>> > Is there any function that can capture this coutning on each movement?
>> >
>> > Numbers Times
>> > 4
>> > 4
>> > 4 3
>> > 5 1
>> > 4
>> > 4 2
>> > 5
>> > 5 2
>> > 4
>> > 4
>> > 4 3
>> > 3 1
>> > 6
>> > 6
>> > 6 3
>> > 7
>> > 7
>> > 7 3
>> > 1
>> > 1 2
>> >
>>

Assuming your number start in A2 and down.

In B2: =IF(A2=A3,"",COUNTIF($A$2:A2,A2)-SUMIF($A$1:A1,A2&"",$B$1:B1))


"Elton Law" wrote:

> Dear Expert,
> Would like to count the frequency each time it shows up .
> Below is the example ....
>
> 4 shows up 3 times ...
> 5 shows up 1 time
> ........ etc etc 7 shows up 3 times, 1 shows up 2 times at the bottom....
> Is there any function that can capture this coutning on each movement?
>
> Numbers Times
> 4
> 4
> 4 3
> 5 1
> 4
> 4 2
> 5
> 5 2
> 4
> 4
> 4 3
> 3 1
> 6
> 6
> 6 3
> 7
> 7
> 7 3
> 1
> 1 2
>

Hi all,

When I tried 2 series below, only teethless mama made it. Thanks indeed.

5
6
6
4
4
3
3
3
1
1
2
3
3
3
3



4
4
4
5
4
4
5
5
4
4
4
3
6
6
6
7
7
7
1
1



"Teethless mama" wrote:

> Assuming your number start in A2 and down.
>
> In B2: =IF(A2=A3,"",COUNTIF($A$2:A2,A2)-SUMIF($A$1:A1,A2&"",$B$1:B1))
>
>
> "Elton Law" wrote:
>
> > Dear Expert,
> > Would like to count the frequency each time it shows up .
> > Below is the example ....
> >
> > 4 shows up 3 times ...
> > 5 shows up 1 time
> > ........ etc etc 7 shows up 3 times, 1 shows up 2 times at the bottom....
> > Is there any function that can capture this coutning on each movement?
> >
> > Numbers Times
> > 4
> > 4
> > 4 3
> > 5 1
> > 4
> > 4 2
> > 5
> > 5 2
> > 4
> > 4
> > 4 3
> > 3 1
> > 6
> > 6
> > 6 3
> > 7
> > 7
> > 7 3
> > 1
> > 1 2
> >