COUNTIFS FUNCTION

Understanding COUNTIFS Formula

COUNTIFS is a function for calculating the count of cells that meet multiple criteria. When corresponding cells meet criteria, COUNTIFS can be used to count values. COUNTIFS supports logical operators (>,<,<>,=) and wildcards (*,?). COUNTIFS can be used with criteria based on dates, numbers, text, and other conditions.

Syntax of COUNTIFS function

The syntax of the COUNTIFS function is as follows:

=COUNTIFS (range1, criteria1, [range2], [criteria2], …)

Note: COUNTIF is used to match single criteria while COUNTIFS is used to match multiple criteria.

Lets us have a better understanding by looking at the example below:

Example For COUNTIFS Function

=COUNTIFS(B4:B11,"JOE",C4:C11,"CANADA") // count sales done by Joe in Canada
=COUNTIFS(B4:B11,B4,C4:C11,C4) // count sales done by Joe in Canada

Using Double quotes (“”) in criteria in COUNTIFS Function

Text values, in general, must be enclosed in double quotes, whereas numbers do not. When a logical operator is used with a number, however, the number and the operator must be enclosed in quotes, as shown below:

=COUNTIFS(B1:B11,100) // count equal to 100
=COUNTIFS(A1:A10,">50") // count greater than 50
=COUNTIFS(A1:A10,"jim",C1:C10,">50") // count sales by jim which are greater than 50

Obtaining Value from another cell using COUNTIFS

The cell reference must be concatenated to an operator when using a value from another cell in a condition. COUNTIFS will count the values in C1:C5 that are less than the value in cell B3 in the example below. The less than operator (text) is surrounded by quotes, but the cell reference is not:

=COUNTIFS(C1:C5,"<"&B3) // count cells less than B3

Using Not equal to in COUNTIFS

Use the “>” operator, in between double quotes, to create “not equal to” criteria (“”). The formula below, for example, counts cells in the range B1:B10 that do not equal “red.”

=COUNTIFS(B1:B10,"<>false") // not "false"

Counting Blank cells using COUNTIFS

COUNTIFS can count both blank and non-blank cells. The following formulas count the number of blank and non-blank cells in the range C1:C5:

=COUNTIFS(C1:C5,"<>") // not blank
=COUNTIFS(C1:C5,"") // blank

Using COUNTIFS with Dates

The simplest way to use COUNTIFS with dates is to use a cell reference to a valid date in another cell. For example, you can use a formula like this to count cells in A1:A10 that contain a date that is greater than the date in B1:

=COUNTIFS(A1:A10, ">"&B1) // count dates greater than A1

The “>” operator is concatenated with the date in B1, but there are no quotes around the cell reference.

=COUNTIFS(A1:A10,"<"&DATE(2020,9,1)) // dates less than 1-Sep-2020

Using Wildcards In COUNTIFS

In criteria, the wildcard characters question mark (? ), asterisk (*), and tilde (~) can be used. An asterisk (*) matches zero or more characters of any kind, while a question mark (?) matches any single character. You can use a formula like this to count cells in A1:A5 that contain the text “apple” anywhere:

=COUNTIFS(A1:A5,"*canada*") // count cells that contain "canada"

The tilde (~) is an escape character to allow you to find literal wildcards. For example, to count a literal question mark (?), asterisk(*), or tilde (~), add a tilde in front of the wildcard (i.e. ~?, ~*, ~~).

OR logic In COUNTIFS

The COUNTIFS function is designed to apply multiple criteria, but the AND logic is used to apply the conditions. This means that if you try to count cells in the same range that contain “red” or “blue,” the result will be zero (0). However, you can use an array constant and the SUM function to count cells using OR logic, as shown below:

=SUM(COUNTIFS(range,{"germany","canada"})) // germany or canada

The formula above will count cells in range that contain “red” or “blue”. Briefly, COUNTIFS returns two counts in an array (one for “red” and one for “blue”) and the SUM function returns the sum as a final result. 

Practice COUNTIFS function below:

The formula can be entered in the highlighted cell. If the formula entered is correct, it shows good job. If the formula is entered incorrectly, the result will be invalid. Please keep in mind that you can only enter formula in the highlighted cells.

Hurray! You know now how to use COUNTIFS function. Practice COUNT, COUNTIF and other functions by checking our articles below.