Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. 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, Excel in Microsoft 365, and 2021
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, Excel in Microsoft 365, and 2021. You can find a version of this tip for the older menu interface of Excel here: Counting Odds and Evens.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
Do you ever have a need to return just a few digits out of a number? This tip shows different formulas you can use to ...
Discover MoreIf your worksheet formulas seem to go on forever, here's a handy way to make them more understandable. (All you need to ...
Discover MoreWant to be able to take information that is in one cell and match it to data that is contained in a table within 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 © 2024 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. 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