Count IF

[sourishghosh]

Hi,

I would like to count an alphabet in a cell until the bucket reaches 0. Also, only first cell should return an value.

EG:

P	3
P
P
0
P	2
P
0

<tbody>
</tbody>

 

[Ron Coderre]

Here's one way...
With your sample data in A2:A8
this regular formula begins the listing of text counts:

B2: =IF(OR(ROW(B1)=1,ISNUMBER(A1)*ISTEXT(A2)),MATCH(1,INDEX(--ISNUMBER(A2:A$50),0),0)-1,"")

(Adjust the bottom of that reference to suit your data.)
Copy that formula down as far as you need.

Is that something you can work with?

 

[sourishghosh]

Thanks, that worked. Just hit one more roadblock. Will need help a formula to identify 'P' for similar cells. Example below. I need to calculate the number of 'P' occurring with regard to Demo, Demo 1, Demo 2. Here it calculates the 'P' from demo till demo 1 i.e. until it hits 0.

Demo P 5 Demo P Demo 1 P Demo 1 P Demo 1 P Demo 2 0 Demo 3 P 4 Demo 3 P Demo 3 P Demo 3 P <tbody> </tbody>

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Ron Coderre]

With your sample data in A2:B11, try this regular formula, copied down:

C2: =IF(OR(ROW(C1)=1,ISNUMBER(B1)*ISTEXT(B2)),MATCH(1,INDEX(--ISERR(SEARCH("demo*P",A2:A$50&B2:B$50)),0),0)-1,"")

Does that help?

 

[sourishghosh]

Sorry I gave the wrong information, instead of 5 demo should count 2 and demo 1 should count as 3. Also, instead of using SEARCH("demo*P can it be changed to referencing a cell, as the text would constantly change.

-Thanks

 

[Ron Coderre]

Ok...I understand now.
This regular formula, copied down, begins the count of "P" values for each "Demo" variation.

C2: =IF((B2<>0)*(A1<>A2),MATCH(1,INDEX(--(A2:A$50&B2:B$50<>$A2&"P"),0),0)-1,"")

Does that help?

 

[sourishghosh]

Great that works! You are awesome. Thanks a lot!!!