How to Count If Cell Contains Number in Excel (6 Easy Ways)

When working with Excel, it’s common to deal with large datasets where you need to count only the numeric entries such as prices, scores, quantities, or measurements. But manually scanning each cell for a number isn’t practical, especially in large spreadsheets. However, Excel offers multiple built-in functions to count cells that contain numbers based on different needs.

In this tutorial, you’ll learn six easy methods to count numeric cells using functions like COUNT, COUNTIF, COUNTIFS, SUBTOTAL, and SUMPRODUCT. We’ll also show how to apply conditions such as greater than or equal to values, reference other cells for dynamic filtering, and combine functions to handle more complex criteria.

The COUNT function is the most straightforward way to count how many cells in a range contain numeric values. It ignores all non-numeric entries like text, blank cells, errors, or logical values. This method is perfect when you just want a quick count of numbers without applying any conditions or filters.

We’ll use a dataset of products and their monthly sales figures. The sales column includes a mix of numeric values and blank or text cells, allowing us to demonstrate how Excel’s COUNT function identifies only the numeric entries.

Steps:

➤ Click on a blank cell where you want the count to appear, such as D2.
➤ Enter the following formula:

Here, B2:B11 refers to the Sales column. The function will count how many of those cells contain numeric values.

➤ Press Enter. Excel will return the number of numeric values in that column, in this case, 7.

This method is best for quickly counting numeric entries in any dataset, especially when you don’t need to apply any specific conditions.

The COUNTIF function allows you to count cells based on a condition. In this case, we can count numeric entries by setting a numeric condition directly like values greater than or equal to zero. This method effectively counts only numbers, excluding both text and blank cells, making it more accurate for real datasets that mix various content types.

Steps:

➤ Click on an empty cell where you want the result, such as D2.
➤ Type the following formula:

Here, B2:B11 is the range covering the Sales column. The condition “>=0” ensures only numeric values that are greater than or equal to zero are counted.

➤ Press Enter.

Excel will return the count of cells in the Sales column that hold numeric values only, ignoring blanks and text entries like “N/A“.

The SUBTOTAL function is especially useful when you’re working with filtered lists or tables in Excel. Unlike the basic COUNT function, SUBTOTAL can be configured to count only the visible numeric cells in a range, excluding any rows hidden by filters or manual hiding. This makes it ideal for dynamic datasets where you want calculations to update as filters change.

In this method, we’ll use function number 102, which tells Excel to count numbers (like COUNT) but only in the currently visible rows.

Steps:

➤ Click on a blank cell where you want the result, such as D2.
➤ Enter the following formula:

Here, B2:B11 is the range containing the Sales values. The code 102 specifies that Excel should count numeric values only, while also ignoring filtered-out or hidden rows.

➤ Press Enter.

Excel will return the total count of numeric entries in the visible portion of the range.

➤ Now try filtering the dataset using the filter buttons on the header row to see how the count changes in real time.

This method is perfect for dashboards or reports where users interactively filter data and expect accurate numeric counts based on visible rows only.

If you need more flexibility or want to work with arrays that involve logical checks, the combination of SUMPRODUCT and ISNUMBER functions is an effective alternative to basic counting functions. This method allows you to count cells containing numeric values even when embedded in complex formulas or when you’re not using traditional functions like COUNT or COUNTIFfunction.

The ISNUMBER function checks whether each cell in the range contains a numeric value. When combined with SUMPRODUCT, we can sum up all the TRUE values (which are converted to 1s), effectively counting the numeric entries.

Steps:

➤ Click on a blank cell where you want to display the result, like D2.
➤ Type the following formula:

Here, ISNUMBER(B2:B11) evaluates each cell and returns TRUE for numbers and FALSE for everything else. The double unary — converts those logical values into 1s and 0s.

➤ Press Enter.

Excel will now count and return the total number of numeric cells in the range B2:B11.

This method lets you make your COUNTIF formula dynamic by using cell references instead of hardcoded conditions. It’s particularly useful when your criteria (like thresholds, limits, or specific values) might change frequently. By referencing another cell, you can simply update that value and let Excel recalculate the count without editing the formula itself.

We’ll demonstrate two ways to use this approach where one uses the full logical operator in a cell (like >1300), and the other splits the logic and referencing the value only.

Steps:

➤ In cell D5, type your condition such as >1300.
➤ Click on a blank cell where you want the count to appear, such as D2.
➤ Type the formula:

This tells Excel to apply the condition stored in D5 to the range B2:B11.

➤ Press Enter.

Now Excel will count how many cells in B2:B11 meet the >300 condition from D5. You can change the condition in D5, and the result in D2 will update automatically.

➤ Alternatively, you can use this formula to directly apply the condition by combining the operator and the reference cell:

Here, the formula uses concatenation (“>”&D5) to dynamically build the condition.

Now Excel will count how many values in B2:B11 are greater than the number in D3. You can change D3 to something else to get new results instantly.

When you want to count cells that meet multiple criteria at the same time, the COUNTIFS function is the best choice. It allows you to specify several conditions across one or more ranges and returns the count of cells where all those conditions are true.

For instance, if you want to count numbers within a certain range or that meet specific value criteria, COUNTIFS handles this easily and precisely.

Steps:

➤ Click on a blank cell where you want to display the result, such as D2.
➤ Type the following formula, adjusting the ranges and criteria as needed:

This formula counts the cells in the range B2:B11 where the values are greater than or equal to 1200 and less than or equal to 1600. Both conditions must be satisfied for a cell to be included in the count.

➤ Press Enter.

Now we have our total count of numeric cells based on our set criterias.

Frequently Asked Questions

What is the difference between COUNTIF and COUNTIFS?

COUNTIF function counts cells based on a single condition, while COUNTIFS  function allows multiple criteria across ranges. COUNTIFS returns the count only when all specified conditions are met simultaneously, making it more versatile for complex filtering.

Can COUNTIFS handle criteria on different columns?

Yes, the COUNTIFS  function can evaluate multiple ranges, each with its own criterion. This allows you to count rows where several columns meet different conditions at once, which is very useful in analyzing complex datasets.

How do I count cells that meet either of two conditions?

The COUNTIFS  function counts cells that meet all conditions (AND logic). To count cells meeting either condition (OR logic), sum multiple COUNTIF function results or use formulas combining SUMPRODUCT and logical functions.

Can COUNTIFS handle wildcards in criteria?

Yes, COUNTIFS  function supports wildcards like * (any characters) and ? (single character) in text criteria. This helps count cells with partial text matches, making it flexible for many text-based conditions.

What happens if one of the COUNTIFS criteria is empty?

If a COUNTIFS criterion is empty, it counts cells with blank values in that range. Be cautious because this can affect results, especially when mixing blank and non-blank criteria across multiple ranges.

Wrapping Up

In this tutorial, we explored six easy and practical methods to count if a cell contains a number in Excel. From simple functions like COUNT and COUNTIF to more dynamic formulas like SUBTOTAL, SUMPRODUCT, and COUNTIFS, each method serves a unique purpose depending on your dataset and filtering needs. Feel free to download the practice file and share your feedback.