# How to count negative numbers in Excel

Use `=COUNTIF(B2:B13,"<0")`. Zero is not negative, so it is left out.

**Excel:** `=COUNTIF(B2:B13,"<0")` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Month | Net cash flow |  | Negative months |
| Jan | 12400 |  |  |
| Feb | -3150 |  |  |
| Mar | 8720 |  |  |
| Apr | -560 |  |  |
| May | 0 |  |  |
| Jun | 4410 |  |  |
| Jul | -9800 |  |  |
| Aug | 15230 |  |  |
| Sep | -240 |  |  |
| Oct | 6300 |  |  |
| Nov | 2050 |  |  |
| Dec | -1175 |  |  |

Formula in E1: `=COUNTIF(B2:B13,"<0")`

Result: 5

## How it works

The criteria `"<0"` keeps values below zero. Five months had negative cash flow; May's zero is neither positive nor negative.

## Other ways

- `=SUMPRODUCT(--(B2:B13<0))`: The same count as a comparison, for combining with other tests.
- `=COUNTIF(B2:B13,"<=0")`: Use when break-even months should count as bad months too. (Also counts May's 0.)

## Common mistakes

- Negatives shown in brackets, like (3,150), are still negative numbers. But if they were imported as text with brackets, COUNTIF ignores them; convert them first.
- A minus sign typed as an en dash (–3150) makes the value text.

## Questions

**How do I count positive numbers?**

Use `=COUNTIF(B2:B13,">0")`.

**How do I add up only the negatives?**

Use SUMIF with the same criteria: `=SUMIF(B2:B13,"<0")`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/count-negative-numbers