Akib Bin Rashid
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.
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.
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.
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.
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
- How to Find Column Number Based on Value in Excel
- Perform VLOOKUP by Using Column Index Number from Another Sheet
- How to Find Column Index Number in Excel (2 Methods)
- Count One Column If Another Column Meets Criteria in Excel (4 Suitable Ways)
- How to Convert Column Number to Letter in Excel (3 Ways)
- Transpose Column to Multiple Rows in Excel (6 Methods)
- How to Count Number of Columns in Excel (3 Easy Methods)
