Count cells that contain odd numbers with SUMPRODUCT

February 28, 2021

In this article, I'll explain how to count cells that contain odd numbers in Excel. This example uses two functions: SUMPRODUCT and MOD.

Before we begin, I suggest that you download the exercise file.

The syntax

If you are familiar with SUMPRODUCT, then the syntax for this formula really simple:

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

where range is the range of numbers to check against your criteria

How MOD function works

MOD function returns the remainder after a number has been divided and takes two parameters: number and divisor.

By definition, an odd number is one that, after being divided by 2, returns a remainder of 1. So, for MOD, we will supply the range of cells where we want to count all odd values as the number parameter and 2 for the divisor.

Step by step SUMPRODUCT formula

Excel formula to count cells that contain odd numbers

In the exercise file, I have prepared a range of 7 numbers which are stored in cells D3:D9. The formula I've used is shown below:

=SUMPRODUCT(--(MOD(D3:D9, 2)=1))

To write the formula pick any blank cell and:

  1. Type =SUMPRODUCT(
  2. Followed by -- to coerce the TRUE/FALSE values to numbers.
  3. Open a parenthesis that will hold your criterion.
  4. Then type MOD(
  5. Select or type the range to be used as your number parameter, followed by a comma. In my example, it's D3:D9.
  6. Insert 2 as the divisor, and ) to close the MOD function.
  7. Then, type =1 to make sure your formula is checking for odd numbers.
  8. Finally, type )) and then press Enter to complete the SUMPRODUCT formula.

The formula will return 4, because there are 4 odd numbers in the range D3:D9.

Remember that this formula will not work if you don't coerce the TRUE/FALSE values to numbers using the double negative.

The formula explained

The nice part about this formula is that the SUMPRODUCT function works directly with arrays. This means you don't need to combine multiple functions and convert your formula to an array using the CTRL + SHIFT + ENTER keyboard combination.

It also means that you can perform a test directly on an array using one or more criteria and count the results.

Since we are looking to count all the odd numbers from our range, we create a condition using the MOD function:

MOD(D3:D9, 2)=1

Because our divisor is 2, MOD will return either a remainder of 1 (for odd numbers) or a remainder of zero (for even numbers).

Then, SUMPRODUCT will run the test against each of the 7 values from cells D3:D9 and return an array of TRUE and FALSE values:


After all the TRUE/FALSE values are coerced (using the -- double negative), we have:

{0, 1, 0, 0, 1, 1, 1}

In the final step, SUMPRODUCT will sum all the values from the coerced array and return 4.

What to do next?

If you are looking to improve your Excel counting skills, I recommend that you also read the following articles about how to:

If you still have questions about how to count cells that contain odd numbers, please post a comment. I reply to all comments that I receive.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Essential Functions

Excel Topics

Excel courses

I have compiled a list of the best Excel online courses. It covers basic and advanced topics, VBA, and Power BI.
View courses
Excel is a very complex application, and understanding it on your own may prove difficult and time-consuming. I have created to help you better understand each function and provide useful examples for different tasks. If you can’t find the answer to your question in one of the published articles, please leave a comment or send me an email, and I will do my best to help you out.

Affiliate Disclosure: When you purchase a product through a link on our website, I may earn an affiliate commission. There are no additional costs to you.
Copyright © 2017-2021