Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. 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 Records Matching Multiple Criteria.

Counting Records Matching Multiple Criteria

by Allen Wyatt
(last updated January 30, 2018)

5

It is not unusual to use Excel to create small databases. For instance, you might keep a list of your poodle-breeders club members in Excel, or you might use it to maintain a list of your active sales contacts. In those instances, you might wonder how you could get a count of the number of records that meet more than one criteria.

Let's say that you are analyzing your membership list, and you wanted to determine a count of the records in which the gender column contains "F" and the city column contains a particular city, such as "Norwood". This, of course, would be helpful because it would answer the burning question of how many female members of your group live in Norwood.

Excel includes a number of worksheet functions that are handy for determining the count of records in a list. How you can use these in a situation where two criteria must be met may not be immediately obvious. Let's examine six specific ways you can achieve the desired goal of female members from Norwood. (Assume that column C is the gender column and column F is the city column.)

The first way to solve the problem is through the use of the COUNTIFS function. If your gender column is column C and your city column is column F, you could use the following formula:

=COUNTIFS(C1:C500,"F",F1:F500,"Norwood")

It looks in the gender column (C) for any cells containing "F" and the city column (F) for any cells containing "Norwood." The result is the number of records that satisfy both criteria.

A second approach is to use the DCOUNTA function. This function allows you to define a set of criteria and use those criteria as the basis for analyzing a list of data. Like all the data functions in Excel, DCOUNTA relies upon three parameters: the data range, the column to use in the comparisons, and the criteria range. To use the function, set up a criteria table in an unused area of your worksheet. For instance, you could set up the following in cells AA1 through AB2: (See Figure 1.)

Figure 1. The criteria table for the DCOUNTA function.

Then, assuming your original data table is in cells A1:K500 (obviously a large poodle breeders' club), then you could use the following to determine the count:

=DCOUNTA(A1:K500,1,AA1:AB2)

The result is a count that meets the criteria you specified in AA1:AB2. Note, as well, that the names you used in AA1 and AB1 must exactly match the labels you used in your table records. When they do, the contents of the Gender column (column C) must be "F" and the contents of the City column (column F) must be "Norwood" in order for the record to be added to the count.

The third solution is to use an array formula to return a single answer. The array formula interestingly uses the SUM function and a little bit of Boolean arithmetic to determine if a record should be counted. Consider the following:

=SUM((C2:C500="F")*(F2:F500="Norwood"))

Simply type the above formula in a cell and then finish it by pressing Ctrl+Shift+Enter; this lets Excel know you are entering an array formula. The formula works because it compares the contents of each row in the array, in turn, according to the criteria specified in the formula. It first compares the contents of the C column with "F"; if it matches, then the comparison returns True, which is the numeric value 1. The contents of column F are then compared to "Norwood". If that comparison is true, then 1 is returned. Thus, 1 * 1 would equal 1, and this is added to the SUM of the array. If either comparison is False, then the numeric value 0 is returned, and 1 * 0 equals 0 (as does 0 * 0 and 0 * 1), which doesn't affect the running SUM.

A fourth and closely related approach is to use the SUMPRODUCT function, but not in an array formula. You could simply use the following in any cell where you wanted to know if the two criteria are met:

SUMPRODUCT((C2:C500="F")*(F2:F500="Norwood"))

Remember, this is not an array formula, so you don't need to press Ctrl+Shift+Enter. The formula works, again, through the magic of Boolean math.

A fifth possible solution, which is a bit more manual than those discussed already, is to use the AutoFilter feature along with a subtotal. Assuming your data records are in A1:K500, with column labels in row 1, you would follow these steps:

  1. Select any cell in the data table.
  2. Display the Data tab of the ribbon.
  3. Click the Filter tool within the Sort & Filter group. Excel should display the AutoFilter drop-down indicators next to each column label in row 1.
  4. Using the drop-down indicator for the gender column (column C), choose F. Your list is automatically filtered to only display female members.
  5. Using the drop-down indicator for the city column (column F), choose Norwood. Your list is automatically displayed to only display female members living in Norwood.
  6. At the bottom of the data table (row 501), in any column desired, enter the following formula:
=SUBTOTAL(3,C2:C500)

This formula causes the SUBTOTAL function to apply the COUNTA function to derive a subtotal. In other words, it returns a count of all records that are displayed by the filtering; this is the count desired.

A sixth approach is to use the Conditional Sum Wizard to come up with a formula for you. (The Conditional Sum Wizard is available as an Excel add-in for Excel 2007 and Excel 2010; it is enabled on most systems by default. It was removed from Excel 2013.) Follow these steps to use the Conditional Sum Wizard:

  1. Select a cell somewhere within the data you want to analyze.
  2. Display the Formulas tab of the ribbon.
  3. In the Solutions group (right side of the ribbon) click Conditional Sum. Excel displays the first step of the Conditional Sum Wizard. The full range of your data should already be shown in the dialog box. (See Figure 2.)
  4. Figure 2. The Conditional Sum Wizard.

  5. Click on Next. Excel displays the next step of the wizard.
  6. In the Column to Sum drop-down list, choose Gender.
  7. In the Column drop-down, choose Gender.
  8. In the Is drop-down, choose the equal sign.
  9. In the This Value drop-down, choose F.
  10. Click on Add. The condition you specified now appears in the dialog box.
  11. In the Column drop-down, choose City.
  12. In the Is drop-down, choose the equal sign.
  13. In the This Value drop-down, choose Norwood.
  14. Click on Add. The second condition now appears in the dialog box.
  15. Click on Next. Excel displays the third step of the wizard.
  16. Choose Copy Just the Formula To a Single Cell.
  17. Click on Next. Excel displays the fourth (and final) step of the wizard.
  18. In the worksheet, click on the cell you want to contain the result of your formula.
  19. Click on Finish.

The result is a formula, appropriate for the conditions you specified, in the cell you selected in step 1.

There are undoubtedly countless other possible solutions you could use to figure out the count of records. These, however, are the "pick of the lot," and allow you to determine the answer quickly and easily.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7759) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Counting Records Matching Multiple Criteria.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Setting Print Titles

Excel allows you to specify certain rows or columns that will be repeated on the pages of a printout. Here's how to set ...

Discover More

Hiding the System Date and Time

Windows normally displays the current date and time at the rightmost side of the Taskbar. You can hide the date and time, ...

Discover More

Forcing Dates Forward

Want to push a date to some pre-defined day of the month? Here are some ways to force the issue.

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Adding Up Tops and Bottoms

When you are working with sequenced values in a list, you’ll often want to take some action based on the top X or ...

Discover More

Starting Out Formulas

When you enter a formula from the keyboard, Excel only knows it is a formula if you start it with an equal sign. You can ...

Discover More

Counting Asterisks in a Column

Excel can be used as a simple database program. If you use asterisks in a column of your database to designate ranking of ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 8 + 0?

2018-01-30 10:29:45

Dave Bonin

Mark Groessl,

Let me take a whack at your question.

I use SUMPRODUCT() often as Allen outlined and I NEVER use SUM() in this fashion.
Why? Because array formulas -- as in Allen's example of SUM() -- are fragile creatures.

I find it's simply too easy to forget to hit Ctrl+Shift+Enter, either when writing the
original formula, or when editing or reviewing it later. The result is a result that is
usually incorrect. Worse yet, the result may be plausible even if it is incorrect, which
may be hard to detect.

If the formulas in my sheet are complicated, then detecting the existence of a broken
array formula and then finding it can be devilishly difficult.

When I first started using array formulas, I wrote several macros to highlight and count
all of the array formulas. Unfortunately, if I broke a formula, these macros didn't identify
the formula that was broken. Instead, I had to hope I could find the broken array formula
by the absence of a marker. Kind of a visual scan and pattern matching.

Array formulas are also a step beyond the skills of most casual users. While we might
understand them, others users of our workbook may not be. This can lead them to
accidentally breaking array formulas while having no idea they did so. Of course, after
their damage is done, the fault is ours because the workbook we gave them had a bug in
it, per their perception.


2018-01-30 09:56:32

Mark Groessl

Allen,
I was just wondering, is there any advantage to using the Array Formula with "SUM", as compared to the Non-Array Formula, with "SUMPRODUCT"? The formulas themselves, except for the "sum" vs "sumproduct" appear to be identical.


2015-09-21 16:50:53

Mark

I agree to the previous comment that this was the ONLY ONE that worked. I have tried consulting different websites for my excel issue and no one but this solved it. It is also very easy to understand and follow.

To the author, Thank you!


2015-08-28 09:48:19

Barry

This is the 5th site I looked at to try and solve my calculation problem and your answer and description is the most direct and easiest to understand of them all for a novice such as myself, but most importantly, it was the only one that WORKED!!!

Thank you!!


2014-06-26 06:33:14

Grateful med student

Analysing data from a recent survey, this saved my bottom as I haven't use excel in years, many thanks.


This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.