# Count IF

#### sourishghosh

##### New Member
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>

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Here's one way...
With your sample data in A2:A8
this regular formula begins the listing of text counts:
Code:
``B2: =IF(OR(ROW(B1)=1,ISNUMBER(A1)*ISTEXT(A2)),MATCH(1,INDEX(--ISNUMBER(A2:A\$50),0),0)-1,"")``

Copy that formula down as far as you need.

Is that something you can work with?

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>

With your sample data in A2:B11, try this regular formula, copied down:
Code:
``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?

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

Ok...I understand now.
This regular formula, copied down, begins the count of "P" values for each "Demo" variation.
Code:
``C2: =IF((B2<>0)*(A1<>A2),MATCH(1,INDEX(--(A2:A\$50&B2:B\$50<>\$A2&"P"),0),0)-1,"")``

Does that help?

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

Replies
2
Views
104
Replies
1
Views
98
Replies
21
Views
811
Replies
6
Views
264
Replies
2
Views
123

1,219,770
Messages
6,150,167
Members
450,937
Latest member
kattyg261

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back