Excel – Find rightmost specific value and return cell count to the left

microsoft excelworksheet-function

I need to find the rightmost cell in a row which contains a "1" and then return the number of cells to the left which contain data. The data will always be a "1" or a "0" and will be populated.

Alternately, it could be viewed as trying to count the number of cells starting at the left until the last "1" is encountered.

In my example, I'm trying to populate cells B9:B13 using a formula.

Any help would be greatly appreciated!

Example

Best Answer

See this example. Data is in B2:E7.

In G2 put the following formula and press CTRL + SHIFT + ENTER from within the formula bar to create an Array Formula. The formula now shall be enclosed in curly braces to indicate that it's any array formula.

=MAX(IF(B2:E2=1,COLUMN(B2:E2),COLUMN(A2)))-COLUMN(A2)

Drag the formula down upto the intended rows. You may have to replace comma (,) with semicolon (;) in case the list separator in your regional settings is ; instead of a ,

The logic is to return the column numbers if there's 1 else the Column number of the Item column. Take max of that and subtract from the Item column number.

enter image description here

Related Question