Hi,

I'm trying to write a formula that will go down a column and count the
number of one value cells that occur between those of another value. For
example, given:

P
S
S
P
S
S
S
S
P
P
S
P

in column A, I'd like a formula in column B that gives:

P
S
S
P 2
S
S
S
S
P 4
P 0
S
P 1

I want the columns opposite the "S" to remain empty. I've been playing with
the =FREQUENCY function, but haven't been able to figure it out.

Thanks,

Demosthenes wrote:
Hi,

I'm trying to write a formula that will go down a column and count the
number of one value cells that occur between those of another value. For
example, given:

P
S
S
P
S
S
S
S
P
P
S
P

in column A, I'd like a formula in column B that gives:

P
S
S
P 2
S
S
S
S
P 4
P 0
S
P 1

I want the columns opposite the "S" to remain empty. I've been playing with
the =FREQUENCY function, but haven't been able to figure it out.

Thanks,


Assuming your data is in A1:A12, put the following in B2 and copy down:

=IF(A2="P",COUNTIF($A$1:A2,"S")-SUM($B$1:B1),"")

Paste this formula in B1 cell
=IF(IF(A1="P",COUNTIF($A$1:$A1,"S"),"")=0,"",IF(A2 ="P",COUNTIF($A$1:$A2,"S"),""))

Paste this formula in B2 cell
=IF(ISNUMBER(IF(A2="P",COUNTIF($A$1:$A2,"S"),"")), IF(A2="P",COUNTIF($A$1:$A2,"S"),"")-SUM($B$1:$B1),IF(A2="P",COUNTIF($A$1:$A2,"S"),""))

Copy the B2 cell and paste it for the remaining cells of B Column

--
Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Demosthenes" wrote:

Hi,

I'm trying to write a formula that will go down a column and count the
number of one value cells that occur between those of another value. For
example, given:

P
S
S
P
S
S
S
S
P
P
S
P

in column A, I'd like a formula in column B that gives:

P
S
S
P 2
S
S
S
S
P 4
P 0
S
P 1

I want the columns opposite the "S" to remain empty. I've been playing with
the =FREQUENCY function, but haven't been able to figure it out.

Thanks,