Mastering COUNTIF with Multiple Criteria: The Ultimate Guide

Grace Jul 17, 2026

Ever found yourself in need of counting instances that meet multiple criteria within a spreadsheet? The COUNTIF function in Excel is a powerful tool for this, but what if you need to apply several conditions simultaneously? Enter the COUNTIFS function, designed to handle multiple criteria. Let's dive into how to use COUNTIFS to tally cells that meet multiple conditions Becky and Bob, marketing managers at TechCorp.

How to countif with multiple criteria in Excel?
How to countif with multiple criteria in Excel?

Becky and Bob have a dataset containing sales figures, regions, and product categories for the past quarter. They want to find out how many times sales were above average in each region, but only for 'Electronics.' Let's break down the steps to achieve this:

How to Use Multiple Criteria in Excel COUNTIF and COUNTIFS
How to Use Multiple Criteria in Excel COUNTIF and COUNTIFS

Setting Up the COUNTIFS Function

Before we start, ensure your dataset is structured like this:

How to use XLOOKUP with Multiple Criteria
How to use XLOOKUP with Multiple Criteria
SalesFiguresRegionsProductCategory
85000EastElectronics

Now, let's start creating the formula to count sales above the average for 'Electronics' in each region.

Excel COUNTIFS and COUNTIF with multiple AND / OR criteria - formula examples
Excel COUNTIFS and COUNTIF with multiple AND / OR criteria - formula examples

Step 1: Calculate Average Sales

First, calculate the average sales for the 'Electronics' category.

journeybegins Here's the formula to achieve this: =AVERAGEIF(B:B, "Electronics", C:C) This calculates the average of cells in column C (SalesFigures) where cells in column B (ProductCategory) are equal to 'Electronics.

How to Use COUNTIF Formula in Excel
How to Use COUNTIF Formula in Excel

Step 2: Apply the COUNTIFS Function

Now, we'll use COUNTIFS to count cells in the 'SalesFigures' column where sales are above the average we just calculated and the 'ProductCategory' is 'Electronics.' Assume our average sales figure for 'Electronics' is $50,000: =COUNTIFS(B:B, "Electronics", C:C, ">50000") This will count all instances where the product category is 'Electronics' and sales were above $50,000.

Applying COUNTIFS to Specific Ranges or Sheets

How to use COUNTIF function different ways in excel - Tamil
How to use COUNTIF function different ways in excel - Tamil

What if you want to apply COUNTIFS to specific ranges within your data or another sheet? You can use 3D referencing. Let's assume Becky has created a similar dataset on another sheet called 'SalesDataTwo': =COUNTIFS(Sheet2!A:B, "Electronics", Sheet2!C:C, ">50000") This applies COUNTIFS to both the 'ProductCategory' and 'SalesFigures' columns on 'SalesDataTwo' sheet for the criteria mentioned.

Step 3: Display Results by Region

How to Use VLOOKUP with Multiple Criteria in Excel
How to Use VLOOKUP with Multiple Criteria in Excel
How to use COUNTIF Function | Microsoft Excel
How to use COUNTIF Function | Microsoft Excel
COUNTIFS with multiple criteria and OR logic
COUNTIFS with multiple criteria and OR logic
How to use COUNT vs. COUNTA vs. COUNTBLANK vs. COUNTIF vs. COUNTIFS
How to use COUNT vs. COUNTA vs. COUNTBLANK vs. COUNTIF vs. COUNTIFS
SUMIF and COUNTIF in Excel
SUMIF and COUNTIF in Excel
a computer screen showing the instructions for counting numbers in order to count them into each column
a computer screen showing the instructions for counting numbers in order to count them into each column
INDEX MATCH Multiple Criteria with Wildcard in Excel (A Complete Guide)
INDEX MATCH Multiple Criteria with Wildcard in Excel (A Complete Guide)
SUMIF and COUNTIF in Excel
SUMIF and COUNTIF in Excel
Google Sheets - CountIF, CountIFS Functions Intro Tutorial in Tamil
Google Sheets - CountIF, CountIFS Functions Intro Tutorial in Tamil

To display results by region, Becky sorts the data by 'Regions' and then uses the COUNTIFS formula, adjusting the range for each region: For East: =COUNTIFS(B:B, "East", C:C, ">50000", D:D, "Electronics")

With these steps, Becky and Bob can now easily analyze sales performance by region, based on multiple criteria.

Advanced Techniques: Nesting COUNTIFS

COUNTIFS doesn't stop at two criteria - you can nest up to 127 conditional tests! Simply add more ranges and criteria within the COUNTIFS parentheses, separated by commas. For instance, to add another condition for sales greater than $70,000: =COUNTIFS(B:B, "Electronics", C:C, ">50000", C:C, "<=70000") This will count sales between $50,000 and $70,000 for 'Electronics' in each region.

Mastering COUNTIFS allows you to extract valuable insights from complex datasets, elevating data analysis at TechCorp from Becky and Bob, and enhancing their decision-making capabilities.