[2 Cases] Count Columns in Excel Until a Certain Value Reached

If you are looking for an Excel formula to count columns, then you are in the right place. Because this blog will teach you to count columns until a certain value is reached in Excel. Here, I have discussed two cases relating to counting columns until a specific value is reached. So, without having any delay, let’s begin.

Case #1: Count Required Columns to Reach a Target Value

In the following scenario, you can see a weekly sales report.

From this sales report, I’m going to show you find the required number of days to meet a target sales.

For demonstration, I have set a Target Sales of $300,000. Now I’m going to use a formula to count the total number of days required to hit this target sales amount of $300,000.

The core concept is, summing up daily sales amount and counting the columns. This continues until the sales amount hit $300,000.

To do this, I’ve used the formula below in cell C5.

Formula

=IFERROR(MATCH(TRUE,SUBTOTAL(9,OFFSET(B2,,,,COLUMN(B2:H2)-COLUMN(B2)+1))>=C4,0)MIN(IF(B2:H2<>"",COLUMN(B2:H2)-COLUMN(B2)+1))+1,"Not Enough Days!")

This formula returns 4. This means a total of 4 days is required to hit the weekly sales amount of $300,000.

How to Use this Formula?

To use the formula you are recommended to update the following arguments:

• B2: This is the first cell of the range within which you want to count the columns.
• B2:H2: This is the cell range within which you want to count the columns.
• C4: It refers to the Target Sales.
• “Not Enough Days!”: This is a text message to show when the weekly sales can’t hit the Target Sales.

Now, look at the scenario below. The Target Sales are set to $900,000. But the weekly sales can’t meet this target. As a result, the formula returned “Not Enough Days”.

🔗 3 Ways to Find Column Index Number in Excel

🔗 2 Ways to Count Columns in Excel for Vlookup

Case #2: Count Total Columns Until a Certain Value is Reached

In this case, I will show you how to count the total number of columns until a specific value is found in Excel.

In the following picture, I’ve presented a weekly sales report. Here, I want to search for the column number that has the Sales Amount, $58,789.

In this regard, I have used the XMATCH function.

Function Syntax

=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

Formula

=XMATCH(C4,B2:H2,0,1)

The formula returns 5. This means the Sales Amount of $58,789 can be found in the 5th column of the specified range.

How to Use this Formula?

Cell C4 is the lookup_value which stores the Sales Amount, $58,789. Next, B2:H2 is the lookup_array. It is the range within which the XMATCH function counts the total number of columns until it finds the Sales Amount, $58,789.

Then 0 and 1 are match_mode and search_mode respectively.

Here, 0 means the XMATCH function looks for an Exact Match between the lookup_value and the lookup_array. You can choose other options as per your requirement.

The last argument 1 instructs the XMATCH function to search from first to last within the lookup array. You can choose other search modes such as “Seach last-to-first”, “Binary search”, etc.

🔗 Count Rows in Excel Until a Certain Value Reached [2 Cases]

🔗 6 Ways to Count One Column in Excel If Another Column Meets Criteria

Conclusion

So, I’ve discussed two cases relating to counting columns in Excel until a certain value is reached. I expect you’ve found this article useful. You can read more articles relating to Excel from the Blog page of our website. Have a fantastic workday!

🔗 3 Methods to Convert Column Number to Letter in Excel

🔗 [Excel VBA] Count Columns & Rows Having Data (10 Editable Codes)