How to Count Backwards in a Row Until a Certain Number is Met in Excel Formula

The basic idea of counting backwards in a row until a certain number is met is to use a formula that can look at the last cell in the row and compare it with a given value. If the last cell matches the value, the formula will count how many cells in the row have the same value, starting from the end and moving backwards. If the last cell does not match the value, the formula will return zero.

One possible formula to achieve this is:

=IF(INDEX(range,1,COLUMNS(range))=value,COUNTIF(OFFSET(range,0,COLUMNS(range)-MATCH(value,range,0)),value),0)

where range is the row of cells to count, and value is the number to match.

This formula works as follows:

• INDEX(range,1,COLUMNS(range)) returns the last cell in the row.
• =value checks if the last cell matches the value.
• IF returns the result based on the condition.
• COUNTIF(OFFSET(range,0,COLUMNS(range)-MATCH(value,range,0)),value) counts how many cells in the row have the same value as the last cell, starting from the end and moving backwards.
• OFFSET(range,0,COLUMNS(range)-MATCH(value,range,0)) returns a range that starts from the last cell that matches the value and ends at the last cell in the row.
• MATCH(value,range,0) returns the position of the last cell that matches the value in the row.
• 0 returns zero if the last cell does not match the value.

Procedures

To use this formula, follow these steps:

• Select a blank cell where you want to display the result.
• Enter the formula with the appropriate range and value arguments.
• Press Enter to confirm the formula.
• Adjust the range and value arguments as needed.

Comprehensive explanation

To illustrate how this formula works, let’s use an example scenario with real numbers.

Suppose you have a row of numbers in cells A1:J1, as shown below:

You want to count how many cells in this row have the value 4, starting from the end and moving backwards, until you reach a different value.

To do this, you can use the formula:

=IF(INDEX(A1:J1,1,COLUMNS(A1:J1))=4,COUNTIF(OFFSET(A1:J1,0,COLUMNS(A1:J1)-MATCH(4,A1:J1,0)),4),0)

This formula will return 3, as there are three cells in the row that have the value 4, starting from the end and moving backwards, until you reach the value 8.

Here is how the formula works in detail:

• INDEX(A1:J1,1,COLUMNS(A1:J1)) returns the last cell in the row, which is J1.
• =4 checks if J1 equals 4, which is true.
• IF returns the result based on the condition, which is the second argument of the IF function.
• COUNTIF(OFFSET(A1:J1,0,COLUMNS(A1:J1)-MATCH(4,A1:J1,0)),4) counts how many cells in the row have the value 4, starting from the end and moving backwards, until you reach a different value.
• OFFSET(A1:J1,0,COLUMNS(A1:J1)-MATCH(4,A1:J1,0)) returns a range that starts from the last cell that matches the value 4 and ends at the last cell in the row, which is J1.
• COLUMNS(A1:J1)-MATCH(4,A1:J1,0) returns the number of columns to offset from the first cell in the row, which is A1, to get to the last cell that matches the value 4, which is I1.
• COLUMNS(A1:J1) returns the number of columns in the row, which is 10.
• MATCH(4,A1:J1,0) returns the position of the last cell that matches the value 4 in the row, which is 9.
• 10-9 returns 1, which is the number of columns to offset from A1 to get to I1.
• OFFSET(A1:J1,0,1) returns the range I1:J1, which is the range that starts from the last cell that matches the value 4 and ends at the last cell in the row.
• COUNTIF(I1:J1,4) counts how many cells in the range I1:J1 have the value 4, which is 2.
• IF returns 2, which is the result of the second argument of the IF function.
• The formula returns 2, which is the final result.

Other approaches

There are other possible formulas that can achieve the same result, such as:

=IFERROR(COUNTIF(OFFSET(A1:J1,0,LOOKUP(2,1/(A1:J1<>4),COLUMN(A1:J1))-1),4),0)

This formula uses the LOOKUP function to find the position of the last cell that does not match the value 4, and then uses the OFFSET and COUNTIF functions to count the cells that match the value 4 after that position.

=IFERROR(COUNTIF(INDEX(A1:J1,MATCH(4,A1:J1,0)):J1,4),0)

This formula uses the INDEX and MATCH functions to return the range that starts from the first cell that matches the value 4 and ends at the last cell in the row, and then uses the COUNTIF function to count the cells that match the value 4 in that range.