# How to count odd and even numbers in Excel

Use MOD to get the remainder after dividing by 2: `=SUMPRODUCT(--(MOD(B2:B11,2)=1))` counts odd numbers; use `=0` for even.

**Excel:** `=SUMPRODUCT(--(MOD(B2:B11,2)=1))` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Address | House no. |  | Odd numbers |
| Elm St | 12 |  |  |
| Elm St | 15 |  |  |
| Elm St | 27 |  |  |
| Elm St | 8 |  |  |
| Elm St | 33 |  |  |
| Elm St | 40 |  |  |
| Elm St | 51 |  |  |
| Elm St | 6 |  |  |
| Elm St | 19 |  |  |
| Elm St | 23 |  |  |

Formula in E1: `=SUMPRODUCT(--(MOD(B2:B11,2)=1))`

Result: 6

## How it works

MOD(number,2) is the remainder after dividing by 2: 1 for odd numbers and 0 for even ones. Comparing the remainders with 1 gives TRUE for the odd house numbers, and SUMPRODUCT counts them: six houses are on the odd side of the street.

## Other ways

- `=SUMPRODUCT(--(MOD(B2:B11,2)=0))`: Counts even numbers. (Counts the 4 even house numbers.)

## Common mistakes

- ISODD and ISEVEN work on single cells, but some Excel versions return #VALUE! when you pass them a whole range. MOD works everywhere.
- Decimals are neither: MOD(4.5,2) is 0.5. Round first if the data can have them.
- Blank cells give a remainder of 0 and are counted as even. Add `*(B2:B11<>"")` to skip them.

## Questions

**How do I add up only the odd numbers?**

Multiply by the values: `=SUMPRODUCT((MOD(B2:B11,2)=1)*B2:B11)`.

**How do I count every other row instead?**

Use the row number: `=SUMPRODUCT(--(MOD(ROW(B2:B11),2)=0))` counts the even-numbered rows.

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-odd-and-even-numbers