How to Count Columns until the Value is Reached in Excel - 2 Steps

The dataset showcases Sales Person , Target and units sold in Quarter-1.

To calculate how many months they took to achieve their Target:

Step 1 – Applying a Combined Formula in the Output Cell

Go to G5 and enter the formula.

=IFERROR(MATCH(TRUE,SUBTOTAL(9,OFFSET(D5,,,,COLUMN(D5:F5)-COLUMN(D5)+1))>=C5,0)-MIN(IF(D5:F5<>"",COLUMN(D5:F5)-COLUMN(D5)+1))+1,"Target Not Fulfilled")

As you enter the SUBTOTAL, you will see a list of function numbers. Choose 9 (to sum the numbers).

Formula Breakdown:

COLUMN(D5) —> returns the column number of D5
- Output: {4}
COLUMN(D5:F5) —> returns the column number of D5:F5
- Output: {4}, {5}, {6}

IF(D5:F5<>””,COLUMN(D5:F5)-COLUMN(D5)+1) —> returns the output of the logical statement.
- IF({TRUE,TRUE,TRUE},{4}, {5}, {6}-{4}+1)
  - Output: {1,2,3}

MIN(IF(D5:F5<>””,COLUMN(D5:F5)-COLUMN(D5)+1)) —> determines the minimum value.
- MIN({1,2,3})
  - Output: {1}

OFFSET(D5,,,,COLUMN(D5:F5)-COLUMN(D5)+1) —> returns a reference to a range with specific height and width calculated by row down and column right.
- OFFSET(250,,,,{4}, {5}, {6}-{4}+1)
  - Output: {250,250,250}

SUBTOTAL(9,OFFSET(D5,,,,COLUMN(D5:F5)-COLUMN(D5)+1))>=C5 —> returns the subtotal. Here, it will sum, as the function_num is 9.
- SUBTOTAL(9,{250,250,250})>=C5
  - Output: {FALSE,TRUE,TRUE}

MATCH(TRUE,SUBTOTAL(9,OFFSET(D5,,,,COLUMN(D5:F5)-COLUMN(D5)+1))>=C5,0)-MIN(IF(D5:F5<>””,COLUMN(D5:F5)-COLUMN(D5)+1))+1 —> returns the relative position of an item that matches a specific value.
- MATCH(TRUE, {FALSE,TRUE,TRUE},0-{1})+1
  - Output: {2}

IFERROR(MATCH(TRUE,SUBTOTAL(9,OFFSET(D5,,,,COLUMN(D5:F5)-COLUMN(D5)+1))>=C5,0)-MIN(IF(D5:F5<>””,COLUMN(D5:F5)-COLUMN(D5)+1))+ 1,”Target Not Fulfilled”) —> finds whether the output is valid. If it isn’t, it will return “Target Not Fulfilled”
- =IFERROR({2}”Target Not Fulfilled”)
  - Output: {2}