COUNTIF Negative Numbers in Excel: Formula and Tips

Formulas & functions · Updated November 19, 2026

If your values are in A2:A20, count the negative numbers with =COUNTIF(A2:A20,"<0"). COUNTIF returns the number of cells whose numeric value is less than zero; it does not count zero or positive numbers.

The Basic COUNTIF Formula

COUNTIF uses a range and a criterion: the range is the cells Excel checks, and the criterion describes what to count. For negative numbers, the criterion is “less than zero,” written as "<0". The comparison operator and zero go inside quotation marks because COUNTIF expects its criterion as text.

For example, with values in A2:A20, enter =COUNTIF(A2:A20,"<0") in an empty cell. If the range contains -12, 0, 7, -3, -0.5, and four other nonnegative values, the result is 3: the three values below zero are counted.

To use the formula:

  1. Select the cell where you want the count to appear.
  2. Type the formula using the range that contains your data.
  3. Press Enter. Excel displays the number of matching cells.

On Windows, you can also select the result cell, type the formula in the formula bar, and press Enter. On Mac, the same formula and steps apply. Excel for the web also supports COUNTIF; menus and some interface details may differ, but the formula syntax is the same.

How the Less-Than-Zero Criterion Works

The criterion "<0" tells Excel to compare each cell with zero and include a cell only when its value is strictly lower. This is different from “less than or equal to zero,” which includes zero as well. Excel treats a numeric value displayed as -0 as zero for this comparison, so it is not counted as negative.

Formatting does not change the underlying value. A cell might show a negative amount in parentheses, such as (25.00), because an accounting or custom number format is applied. If its stored value is -25, COUNTIF counts it. Likewise, a minus sign may be hidden by a custom format even though the underlying value is negative. To check, select the cell and look at its value in the formula bar or temporarily apply a standard number format.

Negative decimals work just like negative whole numbers: -0.01 is less than zero, while 0.01 is not. A cell that looks blank because a formula returns an empty string is not a negative number and does not meet the criterion. Cells containing errors, labels, or other nonnumeric content generally do not satisfy this numeric comparison.

Examples for Common Worksheets

In a budget, a negative amount might represent a refund, credit, or overspend depending on the workbook’s convention. With transaction amounts in D2:D100, the following counts entries below zero. If there are eight negative amounts in that range, the result is 8.

With transaction amounts in D2:D100, enter =COUNTIF(D2:D100,"<0").

In a grade or measurement sheet, negative values can indicate a change below a baseline. With changes in B2:B51, the same test counts how many observations decreased. If the values are percentages stored as actual numbers, a value of -0.03 is included; a value of 3% stored as 0.03 is not.

For a quick visual check, sort the numeric column from smallest to largest or apply a number format that makes signs easy to see. Sorting is only a check, not a replacement for the formula: the count updates when the data changes, while a manually noted count can become stale.

Count Negative Values That Meet Another Condition

If a negative value should count only when a related field also meets a condition, use COUNTIFS. It applies multiple criteria to corresponding ranges and counts rows where all the criteria are true. For instance, you might count negative transactions marked “Approved.”

Assume amounts are in D2:D100 and statuses are in E2:E100. Enter =COUNTIFS(D2:D100,"<0",E2:E100,"Approved"). If six rows have negative amounts and the status Approved, the result is 6. The two ranges must cover the same rows so each amount is checked alongside its own status.

You can add further criteria pairs when needed, such as a department or date condition. Keep the ranges aligned, and make sure the criterion matches the data as stored. For example, “Approved” is not the same criterion as “Pending.” If the status cells contain extra spaces from an import, clean the source data or use a consistent matching approach before relying on the count.

If you want to count negative numbers in one column only when another column is not blank, that is another COUNTIFS criterion. With amounts in D2:D100 and department names in C2:C100, use =COUNTIFS(D2:D100,"<0",C2:C100,"<>"). This counts negative amounts on rows where the department cell is not empty.

Check Numbers Stored as Text

Imported data can look numeric while being stored as text. A leading apostrophe, spaces, or a source-system export can cause this. Because COUNTIF evaluates criteria against cell contents, text-stored numbers can produce unexpected results in a workbook, especially when the source mixes actual numbers and text representations. Do not assume a displayed minus sign alone proves that Excel sees a numeric value.

One simple check is to select a suspicious cell and use Home > Number Format to choose General, then inspect whether its value behaves like a number. You can also test a cell with ISNUMBER. With a suspect value in A2, enter =ISNUMBER(A2) in another cell; TRUE means Excel recognizes the value as a number and FALSE means it does not. This check evaluates one cell at a time.

For a reliable count, clean or convert the source column so numeric entries are genuinely numeric, then apply COUNTIF again. Depending on how the data was imported, options include using Excel’s conversion prompt, the Text to Columns tool, or a deliberate conversion formula in a helper column. Review the result before replacing source data: a column may intentionally contain identifiers or codes that resemble numbers but should remain text.

Choose a Range That Keeps the Count Useful

A fixed range such as A2:A20 is easy to understand and avoids checking an entire column. But if new records are added below row 20, they will not be included. Expand the range when the dataset grows, or put the data in an Excel table so formulas can refer to a named column that adjusts as rows are added.

To create a table in Excel for Microsoft 365 on Windows, select the data and choose Insert > Table, then confirm whether the table has headers. On Mac, use Insert > Table as well; the dialog and ribbon layout can vary slightly. If the table is named Transactions and its amount column is Amount, the structured-reference version is =COUNTIF(Transactions[Amount],"<0"). Replace those names with the actual table and column names in your workbook.

For a one-time check across several columns, select a rectangular range that covers the values you intend to count. With values in B2:D20, use =COUNTIF(B2:D20,"<0"). This counts every negative cell in that rectangle, not just rows that contain at least one negative value. If you need a count of rows where any one of several columns is negative, define the row-level rule first; a simple rectangular COUNTIF counts cells, so a row with two negative entries contributes two.

Common Mistakes and Troubleshooting

  • Leaving out the quotation marks: Write the criterion as "<0". The comparison operator belongs inside the quoted criterion.
  • Using the wrong range: Check that the selected cells include all records but do not accidentally include a total or unrelated column.
  • Expecting zero to count: The less-than operator excludes zero. Use a different criterion if zero should be included.
  • Confusing display with value: Parentheses or a currency symbol may be formatting. Confirm the underlying numeric value.
  • Missing new rows: A fixed reference will not automatically extend beyond its last row. Expand it or use a table column.
  • Counting cells instead of records: A multi-column range counts matching cells, so duplicate negatives in one row can contribute more than one.

If the result is unexpectedly zero, first test a cell you believe is negative with ISNUMBER, then inspect the value itself. Next, check whether the range points to the correct sheet and rows. If the result is too high, look for extra columns, repeated records, or values that are negative but not apparent under the current number format.

Frequently Asked Questions

Does COUNTIF count zero as a negative number?
No. The criterion "<0" means strictly less than zero, so zero is excluded. A value that displays as -0 is numerically equal to zero and is excluded too.

How do I count negative numbers and zero?
Use a criterion for less than or equal to zero. With values in A2:A20, enter =COUNTIF(A2:A20,"<=0"). This includes zero and negative numeric values, but not positive values.

Can COUNTIF count negative percentages or currency amounts?
Yes, if the cells contain numeric values. Formatting as a percentage or currency does not change the comparison. A numeric -5% is below zero, while positive 5% is not.

Can I count negative numbers across multiple columns?
Yes. Give COUNTIF a rectangular range covering those columns; it counts matching cells, not distinct rows. If you need one count per row under a rule such as “any value in this row is negative,” use a row-level helper calculation or another approach designed to count rows.

Why is my negative-looking value not counted?
It may be stored as text, be outside the formula’s range, or only look negative because of formatting or a custom display. Check the underlying value and use ISNUMBER to determine whether Excel recognizes it as numeric.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.