# Count number of "X" before reaching "B"

#### Alphacsulb

##### Active Member
Hello,

Not sure if this would be a formula, but I'm looking to count how many "X" cells are found starting at column A before you reach a cell that equals "B".

Desired outcome:
Row 9: 3
Row 12: 4
Row 15: 5

Any leads would be appreciated.

HTML:
``Excel 2010ABCDEFGHIJKLMNOPQRSTU912640100304XXXBXX12252315,4606005,400169XXXXBXX1532171022,630220022,410700XXXXXBXX[CENTER][COLOR=#161120][B]Master[/B][/COLOR][/CENTER]``

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### Scott Huish

##### MrExcel MVP
=LEN(LEFT(A9,FIND("B",A9&"B")))-LEN(SUBSTITUTE(LEFT(A9,FIND("B",A9&"B")),"X",""))

Copy down.

#### Alphacsulb

##### Active Member
HTML:
``````  ABCDEFGHIJKLMNOPQRSTU9 126  40100304  X  X XBXX12 25231  5,4606005,400169  X  XXXBXX15 321710  22,630220022,410700  XX XXXBXX

Excel tables to the web >>  Excel Jeanie HTML 4``````

#### Rick Rothstein

##### MrExcel MVP
Not sure if this would be a formula, but I'm looking to count how many "X" cells are found starting at column A before you reach a cell that equals "B".
Here is another formula that I think does what you want...

=SUMPRODUCT(0+(MID(LEFT(A9,FIND("B",A9&"B")),ROW(\$1:\$99),1)="X"))

#### Alphacsulb

##### Active Member

=LEN(LEFT(A9,FIND("B",A9&"B")))-LEN(SUBSTITUTE(LEFT(A9,FIND("B",A9&"B")),"X",""))

Copy down.

I believe this is counting the X's only in A9.

I'm not sure why the table is not showing up, but I'd like to count cells that equal X in the row. I can use helper columns if that helps.

#### Rick Rothstein

##### MrExcel MVP
I'm not sure why the table is not showing up, but I'd like to count cells that equal X in the row. I can use helper columns if that helps.
Oh, so that is what your data looks like... that is nowhere near what I thought from reading your original message.

Are the range of cells where the X's and B's can be located always confined to Columns M through U?

Assuming the answer to the above question is "yes", are the only thing that can be in those cells an X, a B or blank?

Will there only be one B per row?

You show Rows 9, 12 and 15... is anything in the intervening rows?

#### Alphacsulb

##### Active Member

The range of cells where X's and B's can be located are typically D through CZ, I modified this example to make it easier to look at.

These cells can have a variety of information in them.

Yes, there is only 1 B per row.

There is nothing in the intervening rows, they are blank but highlighted for printing purposes.

My current thinking is to use help columns, 1 cell that identifies the cell location of where "B" is located in the row, then use the COUNTIF formula to count it based on value of a cell but I'm stuck on writing that formula I would think something like =COUNTIF(A9:VALUEOFCELL(Z9), "X") but I dont know what the correct syntax is for saying "the value of cell Z9" for the range.

#### Scott Huish

##### MrExcel MVP
=COUNTIF(M9:INDEX(M9:U9,MATCH("B",M9:U9,0)),"X")

#### Alphacsulb

##### Active Member
=COUNTIF(M9:INDEX(M9:U9,MATCH("B",M9:U9,0)),"X")

This works! Thank you!!!

Replies
3
Views
54
Replies
3
Views
65
Replies
1
Views
35
Replies
6
Views
38
Replies
14
Views
92