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

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!")`

Formula Explanation

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.

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

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.

Syntax

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

Formula

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

Formula Explanation

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.

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

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!

### How do I count cells in a range in Excel?

To count cells in a range in Excel, use the COUNT function. Simply enter the formula in a cell: Replace “range” with the actual range of cells you want to count. For example: =COUNT(A1:A10)
This formula counts the number of non-empty cells in the specified range (A1 to A10). Adjust the range according to your data.

### Can VLOOKUP lookup multiple columns?

No, VLOOKUP in Excel can only return a value from one column at a time. It is designed to search for a specified value in the leftmost column of a table array and retrieve a corresponding value from a specified column. If you need to retrieve data from multiple columns based on a single lookup criterion, consider using alternative functions like INDEX and MATCH together for a more flexible solution.

### How do you do a VLOOKUP without counting columns?

Simplify your Excel data analysis by learning how to perform a VLOOKUP without the hassle of manual column counting. So, follow these steps:

1. Begin by optimizing your VLOOKUP for efficiency through the use of column references rather than manual counting. Apply this formula: =VLOOKUP(lookup_value, ‘Sheet1’!A:B, MATCH(“HeaderName”, ‘Sheet1’!1:1, 0), FALSE)
2. Replace “HeaderName” with your specific column header to dynamically determine the column number using the MATCH function.
3. Tailor the formula to your needs by inserting actual data, sheet names, and column headers. So, apply this formula: =VLOOKUP(A2, ‘Sheet1’!A:B, MATCH(“ProductName”, ‘Sheet1’!1:1, 0), FALSE)

This optimized VLOOKUP approach streamlines your workflow, ensuring accuracy without the need for manual column counting in Excel.

Related Articles

3 Methods to Convert Column Number to Letter in Excel

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

Rate this post