How to Use COUNTIFS Function

The difference between COUNTIFS and COUNTIF is that COUNTIF function counts cells with a single criterion, while COUNTIFS can count cells with one or multiple criteria.

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 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 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!".

Example 1: To count how many people having letters "en" in the first name and earned less than $100,000.

– Range 1: column A (A2:A9)

– Criteria 1: the first name has letters "en"

– Range 2: column C (C2:C9)

– Criteria 2: "<$100,000"

=COUNTIFS(A2:A9,"*en*", C2:C9,"<$100,000") There are 1 person having letters "en" in the first name with income less than $100,000.

COUNTIFS function

Leave a Reply