Count Cells With Negative Numbers in Excel or Sheets

Updated 9 November 2026 · Standard formula, shown below

To count cells that contain negative numbers, use =COUNTIF(A1:A10,"<0") in Excel or Google Sheets, replacing A1:A10 with the range you want to check. The formula returns the number of cells whose numeric value is below zero; zero, positive numbers, and text are not counted.

Formula

The calculation is a conditional count: count each cell in the selected range if its value is less than zero. In spreadsheet syntax, the formula is =COUNTIF(range,"<0"). Here, range is the group of cells to inspect, and <0 is the criterion meaning “less than zero.”

For example, =COUNTIF(B2:B20,"<0") counts negative numeric values in cells B2 through B20. The result is a count of cells, not the sum of the negative values. The criterion must be in quotation marks because it is a text condition passed to the function.

Worked Example

Suppose cells A1:A10 contain these entries: 12, -4, 0, -2.5, 8, -1, a blank, 3, the text unknown, and -7.

Enter =COUNTIF(A1:A10,"<0") in an empty cell.
The values meeting the condition are -4, -2.5, -1, and -7.
The formula returns 4.

The zero is excluded because it is not less than zero. The blank and text entry are not negative numbers, so neither contributes to the count.

How COUNTIF Handles Negative Values

COUNTIF applies a condition to each cell in a range and counts the cells that satisfy it. The less-than operator makes this useful for finding negative amounts, below-zero temperatures, account balances, or other numeric values with a negative sign.

Negative numbers count: values such as -0.01 and -250 are below zero.
Zero does not count: use <=0 if zero should be included too.
Positive numbers do not count: values greater than zero fail the condition.
Text and empty cells do not count as negative numbers.

The displayed appearance of a value does not determine whether it is negative. A cell might show parentheses, a currency symbol, or rounded digits because of its number format. The underlying numeric value is what matters. For instance, a stored value of -0.004 may display as 0.00 when rounded to two decimal places, but it still meets the <0 test.

Choosing a Range and Variations

Use a range that includes all and only the cells you intend to examine. A fixed range such as A2:A100 is easy to audit. To check an entire column, use =COUNTIF(A:A,"<0"); this is convenient when rows are added, though a bounded range can be more efficient and avoids counting unrelated values elsewhere in the column.

Common variations include:

=COUNTIF(A1:A10,"<=0") counts negative numbers and zeros.
=COUNTIF(A1:A10,">=0") counts zero and positive numbers.
=COUNTIF(A1:A10,"<0")+COUNTIF(C1:C10,"<0") adds the negative counts from two separate ranges.
=COUNTIFS(A1:A10,"<0",B1:B10,"North") counts rows where the value in column A is negative and the corresponding entry in column B is North.

For multiple conditions, each range used by COUNTIFS should have matching dimensions. If the criterion depends on a value in another cell, concatenate the operator with that cell reference; for example, =COUNTIF(A1:A10,"<"&B1) counts values below the number in B1.

Common Problems and Checks

If the result is zero but you expect negative values, check whether those entries are stored as numbers or as text. Imported data can look numeric while remaining text, particularly when it contains an apostrophe, spaces, or inconsistent decimal separators. COUNTIF is intended to compare numeric values; text that merely looks like a negative number may not behave as expected. Convert or clean the data, then check the count again.

Check for hidden spaces or apostrophes. These can make an apparent number a text entry.
Inspect the actual value. A number format can hide decimals or display negatives in parentheses.
Confirm the range. A misplaced row or column reference can omit the values you mean to count.
Use straight quotation marks. The criterion should be written as "<0", not with typographic quotation marks.
Check regional settings if entering formulas manually. Some spreadsheet locales use semicolons between function arguments instead of commas.

Do not confuse counting negative cells with summing them. For a total of the negative values, use =SUMIF(A1:A10,"<0"). That result will usually be negative; COUNTIF instead returns how many qualifying cells there are.

When to Use a Different Formula

COUNTIF is the simplest choice for one range and one condition. Use COUNTIFS when a count depends on more than one condition, such as negative amounts in a particular category or date period. If the range is created by a more complex calculation, a helper column or a formula that evaluates the data first may be easier to maintain.

A related distinction is between a negative number and a number displayed with a minus-like symbol. Formatting changes how a numeric value looks, but it does not turn a positive value into a negative one. Conversely, text such as "-5" may look negative without being stored as a numeric value. When a count seems wrong, verify the cell type as well as its appearance.

Frequently Asked Questions

What is the formula to count negative numbers in Excel?
Use =COUNTIF(A1:A10,"<0"), substituting your own range. It counts numeric values below zero.

Does COUNTIF count zero as a negative number?
No. Zero is not less than zero. To count zeros along with negative values, use the criterion "<=0".

Can I count negative numbers in Google Sheets?
Yes. The same formula works in Google Sheets: =COUNTIF(A1:A10,"<0").

Why is my negative-looking value not counted?
It may be stored as text, or the selected range may not include that cell. Check the underlying value, cell formatting, and formula range.

How do I count negatives in an entire column?
Use a whole-column reference such as =COUNTIF(A:A,"<0"). Choose a bounded range instead if you want to limit the calculation to particular rows.

How do I add the negative numbers instead of counting them?
Use =SUMIF(A1:A10,"<0"). It returns their combined total, while COUNTIF returns how many cells contain qualifying values.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.