Leveraging Conditional Functions in Excel

Conditional functions in Excel let you count, add, or average only the data that meets specific criteria. Among the most useful are COUNTIF, AVERAGEIF, SUMIF, and SUMIFS. They are invaluable whenever you need an answer about part of your data, such as how many deals were approved, the total sales in one region, or the average of orders over a certain size.

This guide walks through each function with worked examples on one small sales table, so you can check every result yourself. It also covers the multi-condition versions, how to write criteria, common mistakes, and a free tester that writes the formula and shows exactly which rows match.

The Sample Data Used in This Guide

Every example below uses this table of ten sales, with headers in row 1 and data in rows 2 through 11. If you type it into a blank sheet, you can follow along and get the same answers.

1. COUNTIF Function

Purpose: counts the number of cells within a range that meet a single criterion.

=COUNTIF(range,criteria)

The range is the group of cells you want to check, and the criteria is the condition a cell must meet to be counted. See Microsoft's COUNTIF reference for the full details.

Example 1: Counting Cells Greater Than a Value

To count how many amounts in E2:E11 are greater than 100, enter this formula in any empty cell and press Enter:

=COUNTIF(E2:E11,">100")

The result is 5, because five amounts (120, 150, 210, 130, and 175) are over 100.

Example 2: Counting Cells With Specific Text

To count how many deals in D2:D11 are marked Approved:

=COUNTIF(D2:D11,"Approved")

The result is 7. The match is not case-sensitive, so approved and APPROVED count too, but the whole cell must match unless you use a wildcard.

2. AVERAGEIF Function

Purpose: calculates the average of cells that meet a single criterion.

=AVERAGEIF(range,criteria,[average_range])

The range is the cells to check against the criteria, and the optional average_range holds the values to average. If you leave average_range out, Excel averages the range itself. See Microsoft's AVERAGEIF reference.

Example 1: Averaging Cells Greater Than a Value

=AVERAGEIF(E2:E11,">100")

This averages only the amounts over 100 and returns 157, which is 785 divided by 5.

Example 2: Averaging Based on Specific Text

To average the amounts in E2:E11 where the status in D2:D11 is Approved:

=AVERAGEIF(D2:D11,"Approved",E2:E11)

The result is about 123.57, which is 865 divided by 7. If no rows match, AVERAGEIF returns a #DIV/0! error, because there is nothing to divide by.

3. SUMIF Function

Purpose: adds the cells that meet a single criterion.

=SUMIF(range,criteria,[sum_range])

The range is checked against the criteria, and the optional sum_range holds the values to add. If you leave sum_range out, Excel adds the range itself. See Microsoft's SUMIF reference.

Example 1: Summing Cells Greater Than a Value

=SUMIF(E2:E11,">100")

This returns 785, the total of the five amounts over 100.

Example 2: Summing Based on Specific Text

To total the amounts where the status is Approved:

=SUMIF(D2:D11,"Approved",E2:E11)

The result is 865. Note the order: in SUMIF, the range to check comes first and the range to add comes last.

4. SUMIFS Function

Purpose: adds the cells that meet multiple criteria.

=SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)

Here the order flips: the sum_range comes first, followed by pairs of a range to check and its criteria. A row is included only if it meets every condition. See Microsoft's SUMIFS reference.

Example 1: Summing Based on Multiple Conditions

To total the amounts where the status is Approved and the amount is over 100:

=SUMIFS(E2:E11,D2:D11,"Approved",E2:E11,">100")

The result is 655: Ana's 120, Cara's 150, Fay's 210, and Ivan's 175. The same column can be both the column you add and one of the columns you test.

Example 2: Summing Sales by Region and Product

To total sales where the region is West and the product is Gadget:

=SUMIFS(E2:E11,B2:B11,"West",C2:C11,"Gadget")

The result is 270, from Ana's 120, Eli's 95, and Jo's 55.

5. COUNTIFS, AVERAGEIFS, MAXIFS, and MINIFS

The same multi-condition pattern works for counting, averaging, and finding extremes. =COUNTIFS(B2:B11,"West",D2:D11,"Approved") returns 4, the number of approved West deals. =AVERAGEIFS(E2:E11,B2:B11,"West",C2:C11,"Gadget") returns 90, the average of those three West gadget sales.

MAXIFS and MINIFS, available in Microsoft 365 and newer versions of Excel, return the largest or smallest value that meets every condition. =MAXIFS(E2:E11,B2:B11,"East") returns 210, the biggest East sale. Unlike AVERAGEIFS, they return 0 rather than an error when nothing matches.

Because the multi-condition versions work perfectly well with just one condition, many people use SUMIFS and COUNTIFS for everything. That way, the argument order is always the same, and adding a second condition later is simple.

Writing Criteria That Work

Most errors with these functions come from the criteria. Text and comparisons go inside quotation marks, and to compare against a cell, join the operator to the cell with an ampersand.

Putting criteria in cells instead of typing them into the formula makes your sheet far more flexible. Put West in H1 and use =SUMIFS(E2:E11,B2:B11,H1), and anyone can change the region without touching the formula. Pair it with a drop-down list and you have a simple summary report.

Common Mistakes and How to Fix Them

Ranges of different sizes. In SUMIFS and the other multi-condition functions, every range must be the same size and shape. B2:B11 with E2:E10 returns a #VALUE! error.

Mixing up the argument order. SUMIF puts the range to add last; SUMIFS puts it first. When in doubt, use SUMIFS.

Operators without quotes. Criteria such as >100 must be written as ">100". To use a cell, write ">"&H1, not ">H1", which Excel treats as the text H1.

Extra spaces and numbers stored as text. "West " with a trailing space does not match "West", and a number stored as text may not match a numeric criterion. TRIM and VALUE fix these at the source.

OR logic. Every condition in SUMIFS must be true at once, which is AND logic. For West or East, add two SUMIFS together: =SUMIFS(E2:E11,B2:B11,"West")+SUMIFS(E2:E11,B2:B11,"East"), which returns 1,015.

Free Conditional Function Tester

Use the tester below to try any of these functions on the sample data. Choose COUNTIFS, SUMIFS, AVERAGEIFS, MAXIFS, or MINIFS, pick a column and criteria for up to two conditions, and the tester writes the formula, highlights every matching row, and shows the result Excel would return. It follows the same rules as Excel: text matches ignore case, the * and ? wildcards work on text, and operators such as >, <=, and <> work on numbers.

Change any value in the table to see how the result responds. Copy to Excel pastes the data and your formula, live, so you can keep experimenting in your own workbook, or print the tester as a reference.

Final Thoughts

The COUNTIF, AVERAGEIF, SUMIF, and SUMIFS functions are essential for calculations based on specific criteria. Whether you need to count, average, or sum data based on one condition or several, they give you a powerful way to analyze and understand your data. By mastering these functions, you will strengthen your data analysis skills and make better-informed decisions.

When your conditional summaries turn into weekly reports on sales, jobs, or hours, Updoot is business management software that tracks that data and reports on it for you, for $5 per user per month. To keep building your Excel skills, try the free Excel courses.

Sources

COUNTIF function (Microsoft Support)

AVERAGEIF function (Microsoft Support)

SUMIF function (Microsoft Support)

SUMIFS function (Microsoft Support)