Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Counting Odds and Evens.
Written by Allen Wyatt (last updated January 27, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365
Pini has a range of whole numbers (let's say C2:J2) and some of the numbers can be odd and some even. He would like a formula to count the number of odd values in the range and another to count the number of even values in the range.
There are a couple of ways to derive the desired counts, without resorting to intermediate values or macros. One way is to use formula, such as the following, to determine a count of odd values:
=SUM((MOD(C2:J2,2)<>0)*1)
If you are using Excel 2019 or earlier, enter the formula using Ctrl+Shift+Enter and you get the desired count. Only a small change is required for the formula to return the count of even numbers:
=SUM((MOD(C2:J2,2)=0)*1)
You could also use SUMPRODUCT to return the same count, as a regular formula. For example, here is the formula to return a count of odd values:
=SUMPRODUCT(--(MOD(C2:J2,2)=1),--(C2:J2<>""))
This is the formula for even values:
=SUMPRODUCT(--(MOD(C2:J2,2)=0),--(C2:J2<>""))
Another advantage of using the SUMPRODUCT approach is that it compensates for possible blank cells in your range. The earlier array formulas will always count blank cells as if they contain an even value.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7987) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Counting Odds and Evens.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 365 applications with VBA programming. Written in clear terms and understandable language, the book includes systematic tutorials and contains both intermediate and advanced content for experienced VB developers. Designed to be comprehensive, the book addresses not just one Office application, but the entire Office suite. Check out Mastering VBA for Microsoft Office 365 today!
Cells in a worksheet can contain different types of information, such as numbers, text, and formulas. If you want to ...
Discover MoreWhen you are working with sequenced values in a list, you’ll often want to take some action based on the top X or ...
Discover MoreEnter a formula (starting with an equal sign) and you may be surprised if Excel doesn’t calculate the formula. Here's a ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2024-02-28 13:56:20
J. Woolley
In my previous comment below I asked, "Do formulas that include an array but return a single result need to be entered using Ctrl+Shift+Enter in older versions of Excel?" I guess it depends on the formula, but determining which formulas qualify is too confusing. Therefore, for versions prior to Excel 2021 you should use Ctrl+Shift+Enter if the formula involves an array. This conforms to the advice of the current Tip and others like it. I apologize for my previous poor advice regarding this subject.
Excel's array formulas and functions are very useful, but their implementation in older versions is too cumbersome; therefore, I suggest anyone who is serious about Excel should upgrade to Excel 2021 or a newer version.
2024-01-27 14:56:28
J. Woolley
Excel versions prior to 2021 do not support dynamic arrays. The Tip's first formula
=SUM((MOD(C2:J2,2)<>0)*1)
includes an array but returns a single result; therefore, I believe it can be entered in the usual way in older versions of Excel. Ctrl+Shift+Enter works but is not necessary.
This formula
=(MOD(C2:J2,2)<>0)*1
returns an array with 1 row and 8 columns, so in older versions it must be entered by first selecting 8 columns then pressing Ctrl+Shift+Enter.
Do formulas that include an array but return a single result need to be entered using Ctrl+Shift+Enter in older versions of Excel? I don't think so, but please tell me if I'm wrong.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here:
Comments