The difference between the COUNTIFS function and the COUNTIF function is that the COUNTIF function counts cells that meet a single criterion. In contrast, the COUNTIFS function can count cells with one or multiple criteria.
Example: You are working with a dataset with the first name in column A, the last name in column B, and the salary in column C.
Formula 1: To count how many people have the letters "en" in their first name and a salary less than $100,000.
=COUNTIFS(A2:A9,"*en*", C2:C9,"<$100,000")
One person has the letter "en" in the first name with an income of less than $100,000.
Formula 2: To count how many people have the letters "W" in their last name and a salary less than $90,000.
=COUNTIFS(A2:A9,"*W*", C2:C9,"<$90,000")
One person has the letter "W" in the last name with an income of less than $90,000.
Notes: The COUNTIFS function
Formula:
= COUNTIFS(Range 1, Criteria 1, Range 2, Criteria 2…)
Explanations:
– Range 1 is required, which is the first data range you need to count;
– Criteria 1 is required, which can be a number, a text string, an expression, or a cell reference;
– Range 2: is optional, which is the second data range that you need to count;
– Criteria 2: is optional, which can be a number, a text string, an expression, or a cell reference.
– You can add other range/criteria pairs, but the maximum number of range/criteria pairs is 127.
Cautions:
Each range must have the same number of rows and columns as the first range criteria; otherwise, you will get the wrong result "#VALUE!".
Practice: COUNTIFS function