How to use COUNTIFS in Excel | Easy Beginner's Guide

In this tutorial, Allison Gonzalez, a Microsoft Certified Trainer from Pragmatic Works, walks beginners through the practical application of the COUNTIFS function in Excel. This guide is part of a broader Excel training series, following a previous episode focused on SUMIFS.

What is COUNTIFS?

The COUNTIFS function in Excel counts the number of cells that meet one or more criteria. It’s especially useful when working with large datasets where manual counting is not feasible.

COUNTIFS Syntax

The basic syntax of the COUNTIFS function is:

=COUNTIFS(range1, criteria1, [range2, criteria2], ...)

You can use up to 127 range/criteria pairs.

Why Use COUNTIFS Over COUNTIF?

• COUNTIF handles only one condition.
• COUNTIFS supports multiple conditions.
• Allison recommends using COUNTIFS consistently, even with a single condition, to simplify learning the function's structure.

Example 1: Count Scores Over 75

Allison demonstrates how to count how many trivia teams scored over 75 using this formula:

=COUNTIFS(F19:F38, ">75")

This counts all rows in the score column that are greater than 75. In the example, the result is 5.

Example 2: Count Scores ≤50 in Week 2

This example introduces two criteria: scores ≤50 and only for Week 2.

=COUNTIFS(F19:F38, "<=50", E19:E38, 2)

This filters the dataset to just Week 2 entries and counts those with a score of 50 or less. The result: 2.

Example 3: Count Names Containing "Excel"

Here, Allison shows how to use wildcards to search for text within cell values. To count team names that contain the word "Excel" anywhere in the name:

=COUNTIFS(B19:B38, "*Excel*")

This returns 6 matches. She also demonstrates how to add another criterion, such as filtering by week:

=COUNTIFS(B19:B38, "*Excel*", E19:E38, 1)

This finds 3 matches for Week 1, and swapping in 2 would find the Week 2 results.

Key Takeaways

• COUNTIFS is ideal for counting entries based on multiple criteria.
• Supports both numeric and text-based filtering.
• Wildcard characters like * can match partial text within strings.
• It’s more scalable and flexible than COUNTIF for complex filtering tasks.

This video is perfect for Excel beginners looking to level up their data analysis skills. For deeper Excel training, viewers are encouraged to explore the Pragmatic Works On-Demand Learning Platform.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Excel and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.