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.
Examples
With the example shown, COUNTIFS can be used to count records using 2 criteria as follows:
Notes:
- Each additional range must have the same number of rows and columns as range1, but ranges do not need to be adjacent. If you supply ranges with a mismatch, you'll get a #VALUE error.
- 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 count with multiple criteria and 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"}))...
To sum the total amount of time over 30 minutes, given a set of times that represent duration, you can use the SUMPRODUCT and TIME functions. In the example shown, the formula in G5 is:
=SUMPRODUCT((times-TIME(0,30,0...
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 cells not equal to this or that, you can use the COUNTIFS function with multiple criteria. In the example shown, there is a simple list of colors in column B. There are 6 cells total with a color, and a few are...
Excel contains a built-in preset for highlighting duplicate values with conditional formatting, but it only works at the cell level. If you want to highlight entire rows that are duplicates you'll need to use your own...
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 configure COUNTIFS (or COUNTIF) with a variable range, you can use the OFFSET function. In the example shown, the formula in B11 is:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"<>")
This formula...
To use COUNTIFS with a variable table column, you can use INDEX and MATCH to find and retrieve the column for COUNTIFS. In the example shown, the formula in H5 is:
=COUNTIFS(INDEX(Table1,0,MATCH(G5,Table1[#Headers],0...
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 count the number of cells that begin with certain text, you can use the COUNTIF function. In the generic form of the formula (above) rng is a range of cells, txt represents the text that cells should begin with, and...
To count times that occur within a certain range, you can use the COUNTIFs function. In the example shown, the formula in E7 is:
=COUNTIFS(B5:B11,">="&E5,B5:B11,"<"&E6)
How this...
To count new customers by month, you can use a helper column and the COUNTIFS function. In the example shown, the formula in H5 is:
=COUNTIFS(new,1,date,">="&G5,date,"<="&EOMONTH(G5,0...
To build a summary table with a count of paired items that appear in a list of existing combinations, you can use a helper column and a formula based on the COUNTIFS function. In the example shown the formula in cell H5...
To mark the first duplicate in a list, you can use a formula based on the COUNTIF function. Optionally, you can flag subsequent duplicates with a different marker. In the example shown the formula in cell C4 is:
=IF(...
To count cells that are not blank in a range, you can use the COUNTA function. In the example shown, D5 contains this formula:
=COUNTA(B5:B9)
How this formula works
COUNTA is fully automatic. When given a range of...
Related videos
In this video, we'll look at how to use the COUNTIFS function to count cells that meet a multiple criteria in a set of data.
Pivot Tables are fantastic tools for summarizing data, but you can also use formulas to build your own summaries using functions like COUNTIF and SUMIF. See how in this 3 minute video.
Related functions
