For example, suppose you have the following data:

P
S
S
P
S
S
S
P
S
S

and you want to count how many "S"s repeat after each "P." That is, you're
looking for output:

"2s" = 2
"3s" = 1

Is there any way to write a function to do that?

Create a helper column (B).
In B2:
=IF(A2="P",0,B1+1)

Now, to get your desired answers
2s:
=COUNTIF(B:B,2)
3s:
=COUNTIF(B:B,3)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Demosthenes" wrote:

For example, suppose you have the following data:

P
S
S
P
S
S
S
P
S
S

and you want to count how many "S"s repeat after each "P." That is, you're
looking for output:

"2s" = 2
"3s" = 1

Is there any way to write a function to do that?

Luke,

This solution will count more 2's than necessary. You have to take
into account that the next value will be 0. So, start with the helper
column suggested by Luke in B2 (assume data in A2:A101):

=IF(A2="P",0,B1+1)

and then,

=SUMPRODUCT((A2:A101=2)*(A3:A102=0))

HTH
Kostis Vezerides

On Aug 3, 4:24*pm, Luke M wrote:
Create a helper column (B).
In B2:
=IF(A2="P",0,B1+1)

Now, to get your desired answers
2s:
=COUNTIF(B:B,2)
3s:
=COUNTIF(B:B,3)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*



"Demosthenes" wrote:
For example, suppose you have the following data:

P
S
S
P
S
S
S
P
S
S

and you want to count how many "S"s repeat after each "P." That is, you're
looking for output:

"2s" = 2
"3s" = 1

Is there any way to write a function to do that?- Hide quoted text -

- Show quoted text -

Try this...

Data in the range A2:A11

D1 = S
C2:Cn = 1,2,3,4,5 etc

Enter this array formula** in D2 and copy down as needed:

=SUM(IF(FREQUENCY(IF(A$2:A$11=D$1,ROW(A$2:A$11)),I F(A$2:A$11<D$1,ROW(A$2:A$11)))=C2,1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

This is what the output will be:

...........C..........D
1.....................S
2........1...........0
3........2...........2
4........3...........1
5........4...........0
6........5...........0



--
Biff
Microsoft Excel MVP


"Demosthenes" wrote in message
...
For example, suppose you have the following data:

P
S
S
P
S
S
S
P
S
S

and you want to count how many "S"s repeat after each "P." That is, you're
looking for output:

"2s" = 2
"3s" = 1

Is there any way to write a function to do that?