Count number of "X" before reaching "B"

Alphacsulb

Active Member
Joined
Mar 20, 2008
Messages
413
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. :eek:

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

Some videos you may like

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
Joined
Mar 17, 2004
Messages
19,955
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
=LEN(LEFT(A9,FIND("B",A9&"B")))-LEN(SUBSTITUTE(LEFT(A9,FIND("B",A9&"B")),"X",""))

Copy down.
 

Alphacsulb

Active Member
Joined
Mar 20, 2008
Messages
413
HTML:
  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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,308
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Mar 20, 2008
Messages
413

ADVERTISEMENT

=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
Joined
Apr 18, 2011
Messages
36,308
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Mar 20, 2008
Messages
413

ADVERTISEMENT

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
Joined
Mar 17, 2004
Messages
19,955
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
How about:
=COUNTIF(M9:INDEX(M9:U9,MATCH("B",M9:U9,0)),"X")
 

Watch MrExcel Video

Forum statistics

Threads
1,108,974
Messages
5,525,990
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top