COUNTIFS in Excel

Allison Gonzalez Dec 13, 2023

Introduction

Welcome back to another episode of our Excel series! Allison Gonzalez, a Microsoft certified trainer at Pragmatic Works, is here to guide you through the wonders of Excel functions. In the last episode, Alison covered the SUMIF function, and this time, she's diving into the COUNTIFS function. If you're ready to level up your Excel game, buckle up for some exciting examples and practical tips.

Understanding COUNTIFS Basics

In this episode, Allison emphasizes the efficiency of COUNTIFS for counting cells or records within a specified range that meet specific criteria. With COUNTIFS, you can have up to 127 pairs of criteria, making it a versatile tool for data analysis.

Example 1: Teams Scoring Higher than 75

Allison kicks off with a practical example involving a list of trivia teams and their scores over two weeks. The task is to count how many teams scored higher than 75. The formula structure is simple:

Range: Select the entire score column (excluding the header).
Criteria: Teams with scores greater than 75.
Formula: =COUNTIFS (F19:F38, ">75")

Allison highlights the simplicity of the formula, making it easy to grasp even for beginners. In this case, five teams scored higher than 75.

Example 2: Dual Criteria – Scores and Week Number

Building on the foundation, Allison introduces a scenario where dual criteria are essential. The objective is to count teams scoring 50 or lower in Week 2. The step-by-step breakdown includes:

First Criteria (Score): Teams scoring less than or equal to 50.
Second Criteria (Week): Teams in Week 2.
Formula: =COUNTIFS (F19:F38, "<=50", E19:E38, 2)

Allison demonstrates the flexibility of COUNTIFS, allowing users to arrange criteria in any order. The result is a count of teams meeting both criteria, which in this case is two teams.

Example 3: Text Criteria – Finding Teams with "Excel" in the Name

Now, Allison spices things up by introducing a text-based criteria scenario. The goal is to count teams with the word "Excel" in their name, regardless of its position in the text. The formula involves:

Text Criteria: Using wildcards (asterisks) to find "Excel" anywhere in the name.
Additional Criteria (Optional): Narrowing down the search to a specific week.
Formula: =COUNTIFS (C19:C38, "*excel*")

Allison cleverly deploys wildcards to capture variations of "Excel" in the team names. The count is initially for all weeks, but users can add a second criteria to focus on a specific week, showcasing the adaptability of COUNTIFS.

Conclusion

Allison wraps up the tutorial by highlighting the simplicity and power of the COUNTIFS function. Whether you're dealing with numerical or text data, COUNTIFS provides a user-friendly yet robust solution. As you embark on your Excel journey, remember to share your thoughts in the comments, and don't forget to like and subscribe to stay updated on all things related to the Power Platform.

Stay Connected and Keep Learning

Allison encourages viewers to explore other topics on the Pragmatic Works channel, covering the entire Power Platform, not just Excel. For those hungry for more knowledge, there's an opportunity to delve into extensive Excel and other Microsoft application training through the Pragmatic Works' on-demand learning platform.