Excel COUNTIFS Function
Summary
The Excel COUNTIFS function returns the count of cells that meet one or more criteria. COUNTIFS can be used with criteria based on dates, numbers, text, and other conditions. COUNTIFS supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.
Purpose
Count cells that match multiple criteria
Return value
The number of times criteria are met
Syntax
=COUNTIFS (range1, criteria1, [range2], [criteria2], ...)
Arguments
- range1 - The first range to evaulate.
- criteria1 - The criteria to use on range1.
- range2 - [optional] The second range to evaluate.
- criteria2 - [optional] The criteria to use on range2.
Usage notes
COUNTIFS counts the number of cells in a range that match supplied criteria. Unlike the COUNTIF function, COUNTIFS can apply more than one set of criteria, with more than one range. Ranges and criteria are applied in pairs, and only the first pair is required. For each additional criteria, you must supply another range/criteria pairs. Up to 127 range/criteria pairs are allowed.
Notes:
- Each additional range must have the same number of rows and columns as range 1, but ranges do not need to be adjacent. If you supply ranges with a mismatch, you'll get a #VALUE errror.
- Non-numeric criteria needs to be enclosed in double quotes but numeric criteria does not. For example: 100, "100", ">32", "jim", or A1 (where A1 contains a number).
- The wildcard characters ? and * can be used in criteria. A question mark matches any one character and an asterisk matches any sequence of characters.
- To find a literal question mark or asterisk, use a tilde (~) in front question mark or asterisk (i.e. ~?, ~*).
COUNTIFS formula examples
To create a summary count by month, you can use the COUNTIFS function and the EDATE function with two criteria.
In the example shown, the formula in G5 is:
=COUNTIFS(dates,">="&F5,dates,"<...
If you want to count rows where two (or more) criteria match, you can use a formula based on the COUNTIFS function.
In the example shown, we want to count the number of orders with a color of "blue" and a quantity > 15...
To count the number of cells that contain values between two numbers in a range of cells, you can use the COUNTIFS function. In the generic form of the formula (above) range represents a range of cells that contain...
To group numeric data into ranges, you can build a summary table and use COUNTIFS to count values at each threshold.
In the example show, we have a list of names and ages and are using the COUNTIFs function to...
To count the number of cells that contain text (i.e. not numbers, not errors, not blank), use the COUNTIF function and a wildcard. In the generic form of the formula (above), rng is a range of cells, and "*" is a...
To build a summary to show course completion status based on a data log, you can use the COUNTIFS function together with the IF function.
In the example shown, the formula in G4 is:
=IF(COUNTIFS(name,$F4,course,G$3...
To create a count of the values that appear in in a list or table, you can use the COUNTIFS function. In the example shown, the formula in D5 is:
=COUNTIFS(B:B,B5,C:C,C5)
How this formula works
The COUNTIFS function...
To build a two-way summary count (i.e. summarizing by rows and columns) you can use the COUNTIFS function.
In the example shown, the formula in G5 is:
=COUNTIFS(dept,$F5,class,G$4)
How this formula works
The...
To build a summary count of non-blank categories, you can use the COUNTIFS function.
In the example show, the formula in G6 is:
=COUNTIFS($B$5:$B$11,F6,$C$5:$C$11,"<>")
How this formula works
This...
To count based on multiple criteria using OR logic, you can use the COUNTIFS function with an array constant.
In the example shown, the formula in H6 is:
=SUM(COUNTIFS(D4:D11,{"complete","pending...
Related videos
Although Pivot Tables are fantastic tools for summarizing data, sometimes you just want a simple, lightweight solution based on formulas. The good news is you can build your own summaries using functions like COUNTIF and SUMIF. See how in this 3 minute video.
Related functions
Download 200+ Excel Shortcuts
Looking good so far. I love the concise and to-the-point nature of the videos. - Mario
