ThisCalculator

Everyday

Count Cells Not Between Two Numbers: 3 Easy Ways

Updated · Standard formula, shown below

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.

To count cells that are not between two numbers in Excel, use the COUNTIFS function with two criteria: one for “less than the lower bound” and one for “greater than the upper bound,” then add them together. For example, to count cells in A1:A10 that are not between 5 and 10, enter =COUNTIFS(A1:A10,"<5")+COUNTIFS(A1:A10,">10").

Formula

The general formula to count cells not between two numbers is:

Count = COUNTIFS(range, “<" & lower) + COUNTIFS(range, ">” & upper)

Where:

  • range – the cell range you are checking (e.g., A1:A10).
  • lower – the smaller number of the interval (e.g., 5).
  • upper – the larger number of the interval (e.g., 10).
  • The & concatenates the comparison operator with the cell reference or value.

This counts values that are strictly less than the lower bound or strictly greater than the upper bound. If you want to include the boundaries themselves (i.e., count cells that are not between 5 and 10 inclusive), use COUNTIFS(range, "<=" & lower-1) + COUNTIFS(range, ">=" & upper+1) or simply adjust the operators.

Worked Example

Using the default values from the calculator above: lower bound = 5, upper bound = 10, and total cells = 10.

Suppose the 10 cells contain: 3, 7, 12, 5, 9, 15, 2, 8, 11, 4.

Cells less than 5: 3, 2, 4 → 3 cells.

Cells greater than 10: 12, 15, 11 → 3 cells.

Total not between = 3 + 3 = 6.

Using the formula: =COUNTIFS(A1:A10,"<5")+COUNTIFS(A1:A10,">10") returns 6.

Why You Might Need This Count

Counting values outside a range is common in quality control, finance, and data cleaning. For instance, you may want to flag transactions that fall outside a normal spending range, or identify measurements that deviate from acceptable tolerances. This count helps you quickly see how many items are outliers without manually filtering.

It is also useful when you need to compare the number of in-range vs. out-of-range entries, such as checking how many students scored below 50 or above 90 on a test.

How to Use COUNTIFS for This

COUNTIFS works with multiple criteria. To count cells not between two numbers, you need two separate conditions because COUNTIFS applies AND logic by default—it would count cells that are both less than 5 AND greater than 10, which is impossible. Therefore, you use two COUNTIFS functions and add them.

Steps:

  1. Select the cell where you want the result.
  2. Type =COUNTIFS(.
  3. Select the range (e.g., A1:A10).
  4. Type a comma, then "<5" (or reference a cell containing 5, like "<"&B1).
  5. Close the first COUNTIFS and add a plus sign.
  6. Repeat with the second condition ">10".
  7. Press Enter.

Example: =COUNTIFS(A1:A10,"<"&B1)+COUNTIFS(A1:A10,">"&B2) where B1=5 and B2=10.

Common Mistakes and How to Avoid Them

  • Using COUNTIF instead of COUNTIFS: COUNTIF can only handle one condition. You would need two COUNTIF functions, not one.
  • Forgetting to include boundaries: Decide whether “between” includes the endpoints. If not, use strict inequalities. If yes, adjust with <= and >=.
  • Mixing up operators: Ensure you use < for lower and > for upper. A common error is reversing them.
  • Not using quotes around criteria: In COUNTIFS, criteria must be in quotes if they contain operators, unless you concatenate with a cell reference.
  • Counting text or blanks: COUNTIFS ignores text and blank cells by default, which is usually what you want. If you need to include them, you’ll need additional logic.

Alternatives: SUMPRODUCT and FILTER

Another robust method is using SUMPRODUCT:

=SUMPRODUCT((A1:A10<5)+(A1:A10>10))

This works because TRUE/FALSE values are treated as 1/0 in arithmetic. It’s a single formula and can be easier to read for some users.

If you are using Excel 365 or Excel 2021, you can use the FILTER function:

=ROWS(FILTER(A1:A10, (A1:A10<5)+(A1:A10>10)))

This returns the count of rows that meet either condition. It’s more flexible if you later want to extract those values.

Frequently Asked Questions

How do I count cells not between two numbers including the boundaries?

To include the lower and upper bounds as “not between,” you would count cells that are less than the lower bound or greater than the upper bound, but also include cells that equal the bounds if you want them excluded. Actually, “not between” typically excludes the bounds if the interval is inclusive. If you want to exclude the bounds, use < and >. If you want to include the bounds in the “not between” count, use <= and >= with the bounds themselves, or simply use < and > with lower-1 and upper+1.

Can I use COUNTIFS with cell references for the bounds?

Yes, you can. For example, =COUNTIFS(A1:A10,"<"&B1)+COUNTIFS(A1:A10,">"&B2) where B1 and B2 contain the lower and upper bounds. This makes the formula dynamic—if you change the bounds, the count updates automatically.

What if my data has text or blank cells?

COUNTIFS ignores text and blank cells by default, so they won’t affect the count. If you need to count them as “not between,” you would need to add extra criteria, such as COUNTIFS(range,"<5")+COUNTIFS(range,">10")+COUNTIF(range,"text"), but that’s rarely needed.

Why does my COUNTIFS formula return 0?

Check that your criteria are in quotes and that the operators are correct. Also, ensure that the range contains numeric values—if your numbers are stored as text, COUNTIFS won’t count them. Use the VALUE function or convert text to numbers.

Is there a way to count cells not between two numbers in Google Sheets?

Yes, the same COUNTIFS and SUMPRODUCT formulas work in Google Sheets. The syntax is identical, so you can use the same examples above.

Can I count not between using a single COUNTIFS?

No, because COUNTIFS applies AND logic between criteria. To count values that are less than lower OR greater than upper, you need two separate COUNTIFS and add them, or use SUMPRODUCT with an OR condition.

Related

Formula used
in t | Lower bound | - | 5 | 0
in u | Upper bound | - | 10 | 0
in n | Count of all cells | - | 10 | 0
out c | Cells not between | - | n - COUNTIFS(range,">=" & t) - COUNTIFS(range,"

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.