Software

Use COUNTIFS() to evaluate multiple count and criteria ranges

Excel's COUNTIFS() function is flexible enough to handle different ranges and data types.

In Count the number of Excel records that fall between two dates, I used the COUNTIFS() function to count the number of dates that fell between two dates (inclusive of the dates themselves). Although the COUNTIFS() function allows you to specify multiple count and criteria ranges, it's flexibility isn't readily apparent. (This function is available in Excel 2007 and 2010.)

Before we explore that idea, let's take a minute to review COUNTIFS(). This function uses the following syntax to specify multiple criteria:

COUNTIFS(countrange1, criteria1, [countrange2, criteria2]...)

But, neither countrangex nor criteriax need reference the same range or data type.

The following Excel sheet illustrates this idea by counting the number of dates that fall between a start and end date. (The one caveat being that this function fails to return the correct value if you transpose the two criteria dates.) The following formula counts the number of dates in A2:A9 that are equal to or greater than the date in F1 and equal to or less than the date in F2:

=COUNTIFS($A$2:$A$9, ">="&$F$1,$A$2:$A$9,"<="&$F$2)

In this case, the count ranges are the same and both criteria are dates. Five dates fall between the start and end dates.

Now, let's suppose that you want to further limit the search to specific personnel (column B). What now? Remember when I said that COUNTIFS() was flexible enough to handle multiple count ranges and and data types? The simplest solution is to add a third count and criteria range to the COUNTIFS(), as shown below. The third set of ranges in this formula refers to the personnel in column B - that's a different count range and the criteria is a string, not a date value.

=COUNTIFS($A$2:$A$9,">="&$F$1,$A$2:$A$9,"<="&$F$2,B$2:B$9,"="&$F$4)

Below, you can see this example evaluated in helper columns. Only one record meets all three criteria expressions. There is one limitation, which becomes more obvious in this format. The count ranges must all be the same size. For instance if you reference A2:A9 and A2:A6,or A2:A9 and B2:B6, the function returns an error.

A sample Excel worksheet is provided to help illustrate.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks