Range Contains Numbers? How to Check in Excel
The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.
This article explains how to check whether a range of cells in Excel contains any numbers, and how to get a clear yes/no answer. You will learn the core formulas, how they work, and how to adapt them for different situations like ignoring errors or checking only visible cells.
Formula
The simplest way to check if a range contains any number is the COUNT function:
=COUNT(range) > 0
This returns TRUE if at least one cell in the range contains a number, and FALSE otherwise. The variables are:
- range – the group of cells you are testing (e.g.,
A1:A10). - COUNT – counts only cells that contain numeric values, including dates and times (which are stored as numbers in Excel).
If you need a literal “Yes” or “No” instead of TRUE/FALSE, wrap it in an IF:
=IF(COUNT(range)>0, "Yes", "No")
Worked Example
Suppose you have the following values in cells A1:A10:
5, “apple”, 12, “”, 3.5, TRUE, 2024-01-15, “7”, “text”, 0
Using the default range size of 10, the formula is:
=COUNT(A1:A10) > 0
Step by step:
- Excel examines each cell in A1:A10.
- It counts cells that contain numbers: 5 (numeric), 12 (numeric), 3.5 (numeric), TRUE (counts as 1), 2024-01-15 (date serial number), 0 (numeric) → total 6.
- It ignores text (“apple”, “7”, “text”), blanks, and logical values are counted because they are numbers.
- Since 6 > 0, the result is
TRUE.
So the range contains at least one number. If you used the IF version, it would return “Yes”.
When to Use This Check
You might need this in many real situations:
- Checking if a data entry row has any numeric input before running calculations.
- Validating that a column of imported data actually contains numbers, not just text.
- Conditionally formatting rows that have at least one numeric value.
- Building dashboards where you want to show a message like “No data” when a range is all text or blank.
The check is especially useful when you combine it with other functions like IF to control what happens next.
How to Read the Result
The formula returns a Boolean value: TRUE means at least one numeric cell exists, FALSE means every cell in the range is text, blank, or an error (unless you handle errors separately). If you use the IF version, you get the custom text you defined.
Note that Excel treats dates, times, and logical values (TRUE/FALSE) as numbers internally. So a cell containing TRUE will make the formula return TRUE. If you want to exclude logical values, you need a more complex formula using SUMPRODUCT and ISNUMBER:
=SUMPRODUCT(--ISNUMBER(range)) > 0
This counts only cells that are truly numeric, ignoring dates and logicals (because ISNUMBER returns TRUE for dates, but not for logicals; to exclude dates you would need to check cell formatting, which is not possible with a simple formula).
Common Mistakes and Pitfalls
- Using COUNTIF with text criteria –
COUNTIF(range,"*")counts text, not numbers. That is the opposite of what you want. - Forgetting that numbers stored as text are not counted – If a cell contains “7” (with an apostrophe or formatted as text), COUNT ignores it. To count those, you need
COUNTIF(range,"*[0-9]*")or convert them to numbers. - Including entire columns –
COUNT(A:A)works but may be slow on large sheets; better to limit to the actual used range. - Errors in the range – If a cell contains an error like
#N/A, COUNT ignores it, but your formula still returns TRUE if there is any number elsewhere. To treat errors as “not a number”, you needSUMPRODUCT(ISNUMBER(range)*ISERROR(range)=0)or useAGGREGATE.
Comparison with Other Methods
There are several ways to check for numbers, each with different behavior:
- COUNT(range) – Counts numbers, dates, times, and logical values. Ignores text, blanks, and errors.
- COUNTA(range) – Counts all non-empty cells, including text and errors. Not useful for checking numbers specifically.
- ISNUMBER(range) – Returns an array of TRUE/FALSE, but you need to wrap it in SUMPRODUCT or SUM to get a single result.
- COUNTIF(range,”<9.99E+307") – Counts numbers that are less than a huge number, effectively counting all numbers, but also counts dates and logicals. This is an old trick, but COUNT is simpler.
For most cases, COUNT is the best balance of simplicity and accuracy.
Alternatives for Special Cases
If you need to check only visible cells (e.g., after filtering), use SUBTOTAL:
=SUBTOTAL(2, range) > 0
The function number 2 is COUNT, which ignores hidden rows. If you need to ignore errors as well, use AGGREGATE:
=AGGREGATE(2, 6, range) > 0
Here, 2 is COUNT and 6 means ignore errors. This returns TRUE if any visible, non-error numeric cell exists.
Frequently Asked Questions
Does COUNT count numbers stored as text?
No. If a cell contains “123” as text (often indicated by a green triangle or left-aligned), COUNT ignores it. To count those, you can use COUNTIF(range,"*[0-9]*") or convert the text to numbers using the “Text to Columns” feature or the VALUE function.
How do I check if a range contains only numbers?
Use =COUNT(range)=COUNTA(range). This returns TRUE if every non-empty cell is a number. If there are blanks, they are ignored. To require no blanks, use =COUNT(range)=ROWS(range)*COLUMNS(range).
Why does my formula return TRUE when the cell has a date?
Because Excel stores dates as serial numbers. For example, January 1, 2024 is stored as 45292. So COUNT counts it as a number. If you want to exclude dates, you need to check the cell format, which is not possible with a simple formula. You could use VBA or a helper column with ISNUMBER and CELL functions, but that is complex.
Can I check a range across multiple sheets?
Yes, you can use a 3D reference if the sheets are in the same workbook, like =COUNT(Sheet1:Sheet3!A1:A10)>0. This counts numbers across all sheets in that range. However, it only works if the sheets are adjacent in the tab order.
What if my range contains errors like #DIV/0?
COUNT ignores errors, so if you have a mix of numbers and errors, the formula still returns TRUE. If you want to return FALSE when there is any error, use =AND(COUNT(range)>0, COUNTIF(range,"#N/A")=0) but that only checks for #N/A. A better way is =SUMPRODUCT(ISNUMBER(range)*ISERROR(range))=0 but that is complex. Usually, it is better to fix the errors first.
Does this work in Google Sheets?
Yes, the COUNT function works the same in Google Sheets. The formulas in this article are compatible with both Excel and Google Sheets, except for AGGREGATE which is Excel-only. In Google Sheets, use SUBTOTAL or FILTER for visible cells.
Related
Formula used
in r | Range size | cells | 10 | 1 out has_num | Contains a number? | - | if(count(r) > 0, 1, 0) | 0
Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.