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.

6 comments
ravibabuy
ravibabuy

I have to match the existing stock data with pending orders for proper allocation of various models of vehicles among seven locations. Please provide the suitable formula with sample exercise.

caceresalexj
caceresalexj

THANK YOU SO MUCH FOR YOUR DETAILED EXPLANATION!

Please keep it up.

kevski_is
kevski_is

Hi I'm using a COUNTIFS formula to count the number of rows between two dates, according to a number of additional criteria, as per the example in this article, but I just can't get it to return anything other than zero. The data is being pulled from SQL, and I have verified that the result should be 14, not 0. I'm already using COUNTIFS for other portions of my report, with success, but now need to incorporate a date range. The formula: =COUNTIFS(Data!C:C,">="&K3,Data!C:C,"

jbenton
jbenton

NB had to edit this 'cos TR comments have problems with certain characters "GE" means "greater than or equal to" "LE" means "less than or equal to" =COUNTIFS($A$2:$A$9,"GE"&$F$1,$A$2:$A$9,"LE"&$F$2,B$2:B$9,"="&$F$4) becomes =SUMPRODUCT(($A$2:$A$9GE$F$1)*($A$2:$A$9LE$F$2)*(B$2:B$9=$F$4)) in fact, I think I'll continue to do it that way; it's simpler, shorter and more flexible

jbenton
jbenton

the other thing is: shouldn't your second criteria be "less than or equal to", not just "equal to"?

jbenton
jbenton

data from other sources doesn't always import correctly check that there are no extraneous characters (esp spaces) make sure dates are in the right format (dd/mm/yy not mm/dd/yy) easiest way to check your formula's working correctly is to manually type in an entry that you know should be included in your criteria