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.
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.
How to Count Columns until Value Reached in Excel: 2 Easy Steps
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.
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}
- IF({TRUE,TRUE,TRUE},{4}, {5}, {6}-{4}+1)
- MIN(IF(D5:F5<>””,COLUMN(D5:F5)-COLUMN(D5)+1)) —> This will determine the minimum value.
- MIN({1,2,3})
- Output: {1}
- MIN({1,2,3})
- 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}
- OFFSET(250,,,,{4}, {5}, {6}-{4}+1)
- 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}
- SUBTOTAL(9,{250,250,250})>=C5
- 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}
- MATCH(TRUE, {FALSE,TRUE,TRUE},0-{1})+1
- 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}
- =IFERROR({2}”Target Not Fulfilled”)
- Then press ENTER. Excel will return the output.
Step 2: Use of AutoFill Feature to Get the Count for Other Rows in Table
- Now use Fill Handle to AutoFill up to G8.
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 I got this by counting the columns with the sum of the target for those months. The formula includes OFFSET, COLUMN, and MIN functions to get the result. The Formula Breakdown part explained these functions in detail.
Practice Workbook
It is always good to practice internalizing a method or technique. That’s why I have attached a practice sheet so that you can learn in Excel to count columns until a value reached.
Download Workbook
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.
<< Go Back to Count Columns | Formula List | Learn Excel