How to count odd and even numbers in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT(--(MOD(B2:B11,2)=1))Use MOD to get the remainder after dividing by 2: =SUMPRODUCT(--(MOD(B2:B11,2)=1)) counts odd numbers; use =0 for even.
- Result on the example
- 6 in
E1 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in E1. Change any input and the result updates, or edit the formula to experiment.
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.
=SUMPRODUCT(--(MOD(B2:B11,2)=1))Result6
B2:B1110 rows × 1 col: 12, 15, 27, 8, …A range: 10 cells in one column.
MOD(B2:B11,2){10 rows × 1 col} 0, 1, 1, 0, …MOD: Remainder after division.- number
B2:B1110 rows × 1 col: 12, 15, 27, 8, … - divisor
2
- number
MOD(B2:B11,2)=1{10 rows × 1 col} FALSE, TRUE, TRUE, FALSE, …The = operator tests whether the two sides are equal.
-(MOD(B2:B11,2)=1{10 rows × 1 col} 0, -1, -1, 0, …Makes the value negative.
--(MOD(B2:B11,2)=1{10 rows × 1 col} 0, 1, 1, 0, …Makes the value negative.
SUMPRODUCT(--(MOD(B2:B11,2)=1))6SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
--(MOD(B2:B11,2)=1{10 rows × 1 col} 0, 1, 1, 0, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(--(MOD(B2:B11,2)=1)). We ran it in Google Sheets mode on the same data and it returns 6.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT(--(MOD(B2:B11,2)=0)) | 4Different result | 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.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Count odd and even numbers
E1, count the odd house numbers in B2:B11.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.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.