COUNTIFS

Countifs, Sumifs, and Averageifs are some of the most used formulas in Excel.

COUNTIFS() allows you to count the values within specified ranges conditionally if criteria is met.

COUNTIFS Formula

=COUNTIFS(criteria_range1, criteria1, [criteria_range2], [criteria2],…)

COUNTIFS will count the values in the range only if the values in the criteria range meets the criteria.

In other words,

=COUNTIFS(1st range to check, with this criteria 1, 2nd range to check, with this criteria 2, …)

Example

In the example above, there are two examples of COUNTIFS(). The first one will count the values in cell range D2:D16, when the values in range D2:D16  start with the letter “S”

In the second example the values in cell range D2:D16 and C2:C16 will be counted when the 2nd character in the text from D2:D16 is a “p” and the year in range C2:C16 is greater than 2006

COUNTIFS Advanced Formulas

Criteria Using Text Wildcards

=COUNTIFS(1st range to check, “P*”)

This example shows how your criteria needs to start with the letter “P”.

=COUNTIFS(1st range to check, “P?”)

This example shows how your criteria needs to start with the letter “P” and the second text character can be anything.

Tagged: /