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.
=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”.
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.
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
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.
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!