Count number of "X" before reaching "B"

[Alphacsulb]

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.

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

 

[Scott Huish]

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

Copy down.

 

[Alphacsulb]

  ABCDEFGHIJKLMNOPQRSTU9 126  40100304  X  X XBXX12 25231  5,4606005,400169  X  XXXBXX15 321710  22,630220022,410700  XX XXXBXX
Spreadsheet FormulasCellFormulaI9=+F9-G9-H9J9=+I9-C9I12=+F12-G12-H12J12=+I12-C12I15=+F15-G15-H15J15=+I15-C15 

Excel tables to the web >>  Excel Jeanie HTML 4

 

[Rick Rothstein]

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]

=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]

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]

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]

How about:
=COUNTIF(M9:INDEX(M9:U9,MATCH("B",M9:U9,0)),"X")

 

[Alphacsulb]

How about:
=COUNTIF(M9:INDEX(M9:U9,MATCH("B",M9:U9,0)),"X")

This works! Thank you!!!