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.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Addresses used in a formula can be either relative or absolute. If you need to switch between the two types of ...
Discover MoreIf you use serial numbers that include both letters and numbers, you might wonder how you can increment the numeric ...
Discover MoreIf you need to randomly match up items in two lists, there are a variety of techniques you can use. Here are a couple of ...
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