How to Count Columns until Value Reached in Excel

Excel is the most widely used tool when it comes to dealing with datasets. It serves us in myriads of ways. In this article, I am going to explain a method that will help you to count columns until a value is reached in Excel. That means I will apply a formula that will return me the number that it takes to reach a specific value.


Download Workbook

This is the dataset that I am going to use. I have some Sales Person along with their Target and units sold in Quarter-1. I will calculate how many months they took to fulfill their Target.

excel count columns until value reached


Use of a Combined Excel Formula to Count Columns until Value Reached

In this section, I will use the IFERROR, MATCH, SUBTOTAL, OFFSET, COLUMN, and MIN functions to count columns until a value is reached in Excel.

Step 1: Applying the Combined Formula in Output Cell

  • Go to cell G5 and write down 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 write down the SUBTOTAL, you will have a list of function numbers. Choose 9 as you need to get the sum of the numbers.

excel count columns until value reached

Formula Breakdown:

  • COLUMN(D5) —> This will return the column number of D5
    • Output: {4}
  • COLUMN(D5:F5) —> This will return the column number of D5:F5
    • Output: {4}, {5}, {6}
  • IF(D5:F5<>””,COLUMN(D5:F5)-COLUMN(D5)+1) —> This will return the output analyzing 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)) —> This will determine the minimum value.
    • MIN({1,2,3})
      • Output: {1}
  • OFFSET(D5,,,,COLUMN(D5:F5)-COLUMN(D5)+1) —> This will return 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. In this case 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 —> It 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”) —> This will find out whether the output is valid or not. If not, it will return “Target Not Fulfilled”
    • =IFERROR({2}”Target Not Fulfilled”)
      • Output: {2}
  • Then press ENTER. Excel will return the output.

Read More: Excel VBA: Count Columns with Data (2 Examples)

Step 2: Use of AutoFill Feature to Get the Count for Other Rows in Table

  • Now use Fill Handle to AutoFill up to G8.

excel count columns until value reached

 This formula determines the Months Required to Fulfill the Target for the salespersons individually. In the case of Alex, the target is fulfilled in 2 months that I got by counting the columns with the sum of the target of those months. The formula includes OFFSET, COLUMN, and MIN functions to get the result. The Formula Breakdown part explained these functions in detail.

Read More: How to Count Columns for VLOOKUP in Excel (2 Methods)


Practice Workbook

It is always good to practice internalizing a method or technic. That’s why I have attached a practice sheet so that you can learn in Excel to count columns until a value reached.


Conclusion

In this article, I have explained an efficient way that you can use Excel to count columns until a value is reached. I hope this will help everyone. And lastly, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.


Related Articles

Akib
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo