Site icon ExcelNotes

How to Count Cells with Multiple Text

The difference between COUNTIFS and COUNTIF is that the COUNTIF function counts cells that meet a single criterion, while 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 the first name with the letter J, and the last name with the letter S

=COUNTIFS(A2:A12,"*J*",B2:B12,"*S*")

Three people meet the criteria: rows 8, 9, and 12.

Formula 2: To count how many people the first name with "on", and the last name with the letter S

=COUNTIFS(A2:A12,"*ON*",B2:B12,"*S*")

Two people meet the criteria: rows 8 and 12.

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

Exit mobile version