Count the frequency for every change in numbers
 

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

Count the frequency for every change in numbers
 

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" skrev i meddelelsen
...
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

Count the frequency for every change in numbers
 

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



--
Regards
Dave Hawley
www.ozgrid.com
"Elton Law" wrote in message
...
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

Count the frequency for every change in numbers
 

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

Count the frequency for every change in numbers
 

In B2 and copied down;

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



--
Regards
Dave Hawley
www.ozgrid.com
"Elton Law" wrote in message
...
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" wrote in message
...
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

Count the frequency for every change in numbers
 

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

Count the frequency for every change in numbers
 

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