ThisCalculator

Everyday

Count Cells with Odd Numbers in Excel

Updated · Standard formula, shown below

The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.

This article explains how to count cells that contain odd numbers in Microsoft Excel and Google Sheets. You will learn the exact formulas to use, how they work, and how to adapt them for different situations like counting odd numbers in a range or with additional criteria.

Formula

The core formula to count odd numbers in a range is:

=SUMPRODUCT(–(MOD(range,2)=1))

Here’s what each part does:

  • MOD(range, 2) – returns the remainder when each cell in the range is divided by 2. Odd numbers give a remainder of 1, even numbers give 0.
  • =1 – checks if that remainder equals 1, producing TRUE for odd numbers and FALSE for even numbers.
  • — – the double unary operator converts TRUE/FALSE values to 1/0.
  • SUMPRODUCT – sums the array of 1s and 0s, giving the total count of odd numbers.

In Google Sheets, you can also use =COUNTIF(range, “odd”) directly, but the SUMPRODUCT version works in both Excel and Sheets.

Worked Example

Let’s use the default range A1:A10 with the following numbers:

5, 12, 7, 9, 3, 8, 15, 4, 11, 6

Step-by-step:

  1. MOD(5,2)=1, MOD(12,2)=0, MOD(7,2)=1, MOD(9,2)=1, MOD(3,2)=1, MOD(8,2)=0, MOD(15,2)=1, MOD(4,2)=0, MOD(11,2)=1, MOD(6,2)=0
  2. Compare to 1: TRUE, FALSE, TRUE, TRUE, TRUE, FALSE, TRUE, FALSE, TRUE, FALSE
  3. Convert: 1, 0, 1, 1, 1, 0, 1, 0, 1, 0
  4. Sum: 1+0+1+1+1+0+1+0+1+0 = 6

The formula returns 6 – there are six odd numbers in the range.

When to Use This

Counting odd numbers is useful in data analysis, inventory checks, scheduling, and any situation where you need to identify or count items with odd identifiers. For example:

  • Counting odd order IDs to check for missing pairs.
  • Finding how many odd-numbered rooms are occupied.
  • Verifying that a list of serial numbers contains the expected number of odd entries.

It’s a simple way to quickly summarize numeric data without manually scanning.

How to Read the Result

The result is a single number – the count of cells in the specified range that contain an odd integer. If the range contains text, blanks, or errors, those are ignored, because MOD returns an error for text, and SUMPRODUCT will treat errors as errors (so you may need to handle them). For clean numeric data, the result is straightforward.

Common Mistakes

  • Using COUNTIF with “odd” – In Excel, COUNTIF does not support “odd” as a criterion; you must use SUMPRODUCT or an array formula. In Google Sheets, COUNTIF does support “odd”.
  • Forgetting the double unary (–) – Without it, SUMPRODUCT may treat TRUE/FALSE as text and return 0.
  • Mixing up even and odd – Make sure the condition is =1, not =0, to count odd numbers.
  • Including empty cells – MOD of an empty cell returns 0, which is even, so they don’t affect the count, but if you have text, you’ll get errors.

Variations and Alternatives

If you need to count odd numbers with additional criteria (e.g., only odd numbers greater than 10), use SUMPRODUCT with multiple conditions:

=SUMPRODUCT((MOD(range,2)=1)*(range>10))

For Excel users, you can also use an array formula (entered with Ctrl+Shift+Enter in older versions):

=SUM((MOD(range,2)=1)*1)

In Google Sheets, you can use COUNTIFS with a helper column, but SUMPRODUCT is simpler.

Limitations

The formula works only with numeric values. If your range contains numbers stored as text, MOD will return a #VALUE! error. To fix that, convert text to numbers first. Also, the formula counts each cell individually; if a cell contains a formula that returns an odd number, it counts that result, not the formula itself.

Frequently Asked Questions

How do I count odd numbers in a column with blank cells?

Blanks are ignored because MOD(blank,2) returns 0, which is even. So the formula works fine; it will not count blanks.

Can I count odd numbers in non-adjacent cells?

Yes, you can list them separately in the formula, like =SUMPRODUCT(–(MOD(A1,2)=1))+SUMPRODUCT(–(MOD(C1,2)=1)), but it’s easier to use a single range if possible.

What if my numbers are in a row instead of a column?

The formula works the same for rows. Just change the range, e.g., =SUMPRODUCT(–(MOD(A1:J1,2)=1)).

Does this work in Google Sheets?

Yes, the SUMPRODUCT formula works identically in Google Sheets. You can also use =COUNTIF(range, “odd”) for a simpler approach.

How do I count even numbers instead?

Simply change the condition to =0: =SUMPRODUCT(–(MOD(range,2)=0)).

Why does my formula return #VALUE!?

This usually happens if the range contains text or errors. Use =SUMPRODUCT(–(MOD(IF(ISNUMBER(range),range,0),2)=1)) as an array formula to ignore non-numeric cells, or clean your data.

Related

Formula used
in r | Range | - | A1:A10 | 0
out count | Count of odd numbers | - | SUMPRODUCT(--(MOD(r,2)=1)) | 0

Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work

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