How to Count Columns until Value Reached in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

excel count columns until value reached


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.

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.


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 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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Akib Bin Rashid
Akib Bin Rashid

AKIB BIN RASHID, a materials and metallurgical engineer, is passionate about delving into Excel and VBA programming. To him, programming is a valuable time-saving tool for managing data, files, and internet-related tasks. Proficient in MS Office, AutoCAD, Excel, and VBA, he goes beyond the fundamentals. Holding a B.Sc in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, MD AKIB has transitioned into a content development role. Specializing in creating technical content centred around Excel and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo