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

D

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?
 
L

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)
 
V

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top