How do I create a function that counts number of cells in a series

[Demosthenes]

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 M]

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)

 

[vezerid]

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

 

[T. Valko]

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)),IF(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