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.
= COUNTIFS(Range 1, Criteria 1, Range 2, Criteria 2…)
– 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.
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.