Advanced Excel Chapter 4: SUMIFS & COUNTIFS Examples - Tutorial Rays
  • July 18, 2026

Advanced Excel Chapter 4: SUMIFS & COUNTIFS Examples

AI Reading

Quick summary of this article

Preparing the AI summary…

Conditional Aggregation Functions with Practical Examples

Conditional aggregation functions calculate totals, counts, averages, maximums and minimums only when selected conditions are satisfied. They are essential for preparing sales reports, attendance summaries, payroll sheets, inventory reports and management dashboards.

In this chapter, you will learn SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS, MAXIFS and MINIFS through practical business examples.

Learning Objectives

After completing this chapter, you should be able to:

  • Calculate totals based on one or multiple conditions.
  • Count records that satisfy selected criteria.
  • Calculate conditional averages.
  • Find the highest and lowest values within a category.
  • Use text, number and date criteria correctly.
  • Use wildcards for partial-text matching.
  • Build region-wise, product-wise and employee-wise reports.
  • Create dynamic summaries using criteria cells.
  • Handle blank and nonblank records.
  • Prepare an interactive sales-summary report.

1. Understanding Conditional Aggregation

Normal aggregation functions calculate values from an entire range. Conditional aggregation functions calculate only the records that meet specified conditions.

Normal SUM Example

=SUM(SalesData[TotalSales])

This formula calculates sales from every row.

Conditional SUM Example

=SUMIF(SalesData[Region],"East",SalesData[TotalSales])

This formula calculates sales only for the East region.

Functions Covered in This Chapter

Function Purpose Number of conditions
SUMIF Add values One condition
SUMIFS Add values Multiple conditions
COUNTIF Count records One condition
COUNTIFS Count records Multiple conditions
AVERAGEIF Calculate an average One condition
AVERAGEIFS Calculate an average Multiple conditions
MAXIFS Find the highest value One or multiple conditions
MINIFS Find the lowest value One or multiple conditions

2. Create the Sales Dataset

Create a worksheet named Sales_Data and enter the following records:

OrderID OrderDate Salesperson Region Product Category Quantity TotalSales Status
ORD-1001 01-Jul-2026 Rahul East Laptop Computer 2 90000 Completed
ORD-1002 02-Jul-2026 Priya North Monitor Computer 4 48000 Completed
ORD-1003 03-Jul-2026 Imran West Keyboard Accessories 10 15000 Pending
ORD-1004 04-Jul-2026 Neha South Printer Office Equipment 3 55500 Completed
ORD-1005 05-Jul-2026 Rahul East Mouse Accessories 15 12000 Completed
ORD-1006 06-Jul-2026 Priya North Laptop Computer 1 45000 Pending
ORD-1007 07-Jul-2026 Imran West Monitor Computer 5 60000 Completed
ORD-1008 08-Jul-2026 Neha South Keyboard Accessories 8 12000 Cancelled
ORD-1009 09-Jul-2026 Rahul East Printer Office Equipment 2 37000 Completed
ORD-1010 10-Jul-2026 Priya North Mouse Accessories 20 16000 Completed

Convert the range into an Excel Table using Ctrl + T and name it SalesData.

3. Using the SUMIF Function

SUMIF adds values when one condition is satisfied.

SUMIF Syntax

=SUMIF(range,criteria,[sum_range])

Arguments

  • range – The cells that Excel should test.
  • criteria – The condition that must be satisfied.
  • sum_range – The values that Excel should add.

Calculate Total Sales for the East Region

=SUMIF(
SalesData[Region],
"East",
SalesData[TotalSales]
)

Calculate Total Sales for Laptop

=SUMIF(
SalesData[Product],
"Laptop",
SalesData[TotalSales]
)

Calculate Sales Completed by Rahul

SUMIF can check only one condition. Therefore, this formula calculates all Rahul’s sales regardless of order status:

=SUMIF(
SalesData[Salesperson],
"Rahul",
SalesData[TotalSales]
)

To check both salesperson and status, use SUMIFS.

4. Using Criteria from a Cell

Instead of typing the condition directly inside a formula, you can refer to a cell. This makes the report interactive.

Suppose the selected region is entered in cell B2.

=SUMIF(
SalesData[Region],
B2,
SalesData[TotalSales]
)

When the value in B2 changes from East to North, the result updates automatically.

Create a Region Dropdown

  1. Select cell B2.
  2. Go to Data → Data Validation.
  3. Select List.
  4. Enter the region names in the Source box.
  5. Click OK.
East,West,North,South

5. SUMIF with Numerical Criteria

Calculate Sales Greater Than ₹50,000

=SUMIF(
SalesData[TotalSales],
">50000",
SalesData[TotalSales]
)

Calculate Sales Less Than or Equal to ₹20,000

=SUMIF(
SalesData[TotalSales],
"<=20000",
SalesData[TotalSales]
)

Use a Cell as the Limit

Suppose the minimum sales value is stored in B3.

=SUMIF(
SalesData[TotalSales],
">="&B3,
SalesData[TotalSales]
)

The comparison operator must be enclosed within quotation marks and joined to the cell reference using &.

6. Using the SUMIFS Function

SUMIFS adds values when multiple conditions are satisfied. All supplied conditions work together using AND logic.

SUMIFS Syntax

=SUMIFS(
sum_range,
criteria_range1,
criteria1,
criteria_range2,
criteria2
)

Important: In SUMIFS, the sum range is the first argument. In SUMIF, it is normally the last argument.

East Region Completed Sales

=SUMIFS(
SalesData[TotalSales],
SalesData[Region],
"East",
SalesData[Status],
"Completed"
)

Rahul’s Completed Sales

=SUMIFS(
SalesData[TotalSales],
SalesData[Salesperson],
"Rahul",
SalesData[Status],
"Completed"
)

Computer Sales in the North Region

=SUMIFS(
SalesData[TotalSales],
SalesData[Region],
"North",
SalesData[Category],
"Computer"
)

Completed Laptop Sales in the East Region

=SUMIFS(
SalesData[TotalSales],
SalesData[Region],
"East",
SalesData[Product],
"Laptop",
SalesData[Status],
"Completed"
)

7. Creating a Dynamic SUMIFS Report

Suppose the report contains the following criteria cells:

Cell Selected value
B2 Region
B3 Product
B4 Status

Dynamic Sales Formula

=SUMIFS(
SalesData[TotalSales],
SalesData[Region],
B2,
SalesData[Product],
B3,
SalesData[Status],
B4
)

Create dropdowns in B2, B3 and B4. Users can then change the criteria without editing the formula.

8. SUMIFS with Date Conditions

Excel stores dates as serial numbers, so dates can be tested using comparison operators.

Calculate Sales on or After 5 July 2026

=SUMIFS(
SalesData[TotalSales],
SalesData[OrderDate],
">="&DATE(2026,7,5)
)

Calculate Sales Between Two Dates

Suppose the start date is stored in B5 and the end date is stored in B6.

=SUMIFS(
SalesData[TotalSales],
SalesData[OrderDate],
">="&B5,
SalesData[OrderDate],
"<="&B6
)

Calculate Sales for the Current Month

=SUMIFS(
SalesData[TotalSales],
SalesData[OrderDate],
">="&EOMONTH(TODAY(),-1)+1,
SalesData[OrderDate],
"<="&EOMONTH(TODAY(),0)
)

Calculate Sales for the Current Year

=SUMIFS(
SalesData[TotalSales],
SalesData[OrderDate],
">="&DATE(YEAR(TODAY()),1,1),
SalesData[OrderDate],
"<="&DATE(YEAR(TODAY()),12,31)
)

9. Creating OR Logic with SUMIFS

SUMIFS normally uses AND logic. To calculate East or North sales, add separate SUMIFS calculations.

=SUMIFS(
SalesData[TotalSales],
SalesData[Region],
"East"
)
+
SUMIFS(
SalesData[TotalSales],
SalesData[Region],
"North"
)

Modern Excel Array Method

=SUM(
SUMIFS(
SalesData[TotalSales],
SalesData[Region],
{"East","North"}
)
)

The array contains both regions, and SUM combines the returned totals.

10. Using the COUNTIF Function

COUNTIF counts cells that satisfy one condition.

COUNTIF Syntax

=COUNTIF(range,criteria)

Count Orders from the East Region

=COUNTIF(SalesData[Region],"East")

Count Completed Orders

=COUNTIF(SalesData[Status],"Completed")

Count Laptop Orders

=COUNTIF(SalesData[Product],"Laptop")

Count Sales Greater Than ₹50,000

=COUNTIF(SalesData[TotalSales],">50000")

Count Sales Using a Criteria Cell

=COUNTIF(SalesData[Region],B2)

11. Using the COUNTIFS Function

COUNTIFS counts records that satisfy multiple conditions.

COUNTIFS Syntax

=COUNTIFS(
criteria_range1,
criteria1,
criteria_range2,
criteria2
)

Count Completed Orders in the East Region

=COUNTIFS(
SalesData[Region],
"East",
SalesData[Status],
"Completed"
)

Count Priya’s Computer Orders

=COUNTIFS(
SalesData[Salesperson],
"Priya",
SalesData[Category],
"Computer"
)

Count Orders with Sales Between ₹20,000 and ₹60,000

=COUNTIFS(
SalesData[TotalSales],
">=20000",
SalesData[TotalSales],
"<=60000"
)

Count Completed Orders Between Two Dates

=COUNTIFS(
SalesData[OrderDate],
">="&B5,
SalesData[OrderDate],
"<="&B6,
SalesData[Status],
"Completed"
)

12. Counting Blank and Nonblank Cells

Count Blank Status Cells

=COUNTIF(SalesData[Status],"")

Count Nonblank Status Cells

=COUNTIF(SalesData[Status],"<>")

Count Records Where the Salesperson Is Not Rahul

=COUNTIF(SalesData[Salesperson],"<>Rahul")

Count Completed Records with a Nonblank Salesperson

=COUNTIFS(
SalesData[Status],
"Completed",
SalesData[Salesperson],
"<>"
)

13. Using Wildcards with COUNTIF and SUMIF

Wildcards allow formulas to match partial text.

Wildcard Meaning Example
* Any number of characters "Lap*"
? Any single character "P-100?"
~ Matches an actual wildcard character "~*"

Count Products Starting with “Lap”

=COUNTIF(SalesData[Product],"Lap*")

Count Products Containing “top”

=COUNTIF(SalesData[Product],"*top*")

Calculate Sales for Products Containing “Print”

=SUMIF(
SalesData[Product],
"*Print*",
SalesData[TotalSales]
)

Use a Search Word from a Cell

Suppose the search word is entered in cell B7.

=COUNTIF(
SalesData[Product],
"*"&B7&"*"
)

14. Using the AVERAGEIF Function

AVERAGEIF calculates the average of values that satisfy one condition.

AVERAGEIF Syntax

=AVERAGEIF(range,criteria,[average_range])

Average Sales in the East Region

=AVERAGEIF(
SalesData[Region],
"East",
SalesData[TotalSales]
)

Average Sales for Laptop Orders

=AVERAGEIF(
SalesData[Product],
"Laptop",
SalesData[TotalSales]
)

Average of Completed Sales

=AVERAGEIF(
SalesData[Status],
"Completed",
SalesData[TotalSales]
)

15. Using the AVERAGEIFS Function

AVERAGEIFS calculates an average when multiple conditions are satisfied.

AVERAGEIFS Syntax

=AVERAGEIFS(
average_range,
criteria_range1,
criteria1,
criteria_range2,
criteria2
)

Average Completed Sales in the East Region

=AVERAGEIFS(
SalesData[TotalSales],
SalesData[Region],
"East",
SalesData[Status],
"Completed"
)

Average Computer Sales in the North Region

=AVERAGEIFS(
SalesData[TotalSales],
SalesData[Region],
"North",
SalesData[Category],
"Computer"
)

Average Sales Between Two Dates

=AVERAGEIFS(
SalesData[TotalSales],
SalesData[OrderDate],
">="&B5,
SalesData[OrderDate],
"<="&B6
)

Handle Cases Where No Records Match

=IFERROR(
AVERAGEIFS(
SalesData[TotalSales],
SalesData[Region],
B2,
SalesData[Status],
B4
),
0
)

16. Using the MAXIFS Function

MAXIFS returns the highest value that satisfies one or more conditions.

MAXIFS Syntax

=MAXIFS(
max_range,
criteria_range1,
criteria1
)

Highest Sale in the East Region

=MAXIFS(
SalesData[TotalSales],
SalesData[Region],
"East"
)

Highest Completed Computer Sale

=MAXIFS(
SalesData[TotalSales],
SalesData[Category],
"Computer",
SalesData[Status],
"Completed"
)

Highest Sale by Rahul

=MAXIFS(
SalesData[TotalSales],
SalesData[Salesperson],
"Rahul"
)

Highest Sale Between Two Dates

=MAXIFS(
SalesData[TotalSales],
SalesData[OrderDate],
">="&B5,
SalesData[OrderDate],
"<="&B6
)

17. Using the MINIFS Function

MINIFS returns the lowest value that satisfies one or more conditions.

MINIFS Syntax

=MINIFS(
min_range,
criteria_range1,
criteria1
)

Lowest Sale in the North Region

=MINIFS(
SalesData[TotalSales],
SalesData[Region],
"North"
)

Lowest Completed Accessories Sale

=MINIFS(
SalesData[TotalSales],
SalesData[Category],
"Accessories",
SalesData[Status],
"Completed"
)

Lowest Sale by Priya

=MINIFS(
SalesData[TotalSales],
SalesData[Salesperson],
"Priya"
)

18. Find the Highest-Selling Product

MAXIFS returns the maximum sales value, but it does not directly return the associated product name. Combine MAXIFS with XLOOKUP.

Highest Completed Sales Value

=MAXIFS(
SalesData[TotalSales],
SalesData[Status],
"Completed"
)

Product with the Highest Completed Sale

=XLOOKUP(
MAXIFS(
SalesData[TotalSales],
SalesData[Status],
"Completed"
),
SalesData[TotalSales],
SalesData[Product],
"Not Found"
)

Salesperson Responsible for the Highest Sale

=XLOOKUP(
MAX(SalesData[TotalSales]),
SalesData[TotalSales],
SalesData[Salesperson],
"Not Found"
)

19. Excluding Selected Records

Calculate Sales Excluding Cancelled Orders

=SUMIFS(
SalesData[TotalSales],
SalesData[Status],
"<>Cancelled"
)

Count Orders Excluding the East Region

=COUNTIFS(
SalesData[Region],
"<>East"
)

Average Sales Excluding Pending Orders

=AVERAGEIFS(
SalesData[TotalSales],
SalesData[Status],
"<>Pending"
)

20. Create a Region-Wise Summary Report

Create a worksheet named Sales_Summary and enter the regions in column A.

Region Total Sales Orders Average Sale Highest Sale Lowest Sale
East Formula Formula Formula Formula Formula
North Formula Formula Formula Formula Formula
West Formula Formula Formula Formula Formula
South Formula Formula Formula Formula Formula

Total Sales Formula

=SUMIF(
SalesData[Region],
A2,
SalesData[TotalSales]
)

Number of Orders

=COUNTIF(
SalesData[Region],
A2
)

Average Sale

=IFERROR(
AVERAGEIF(
SalesData[Region],
A2,
SalesData[TotalSales]
),
0
)

Highest Sale

=MAXIFS(
SalesData[TotalSales],
SalesData[Region],
A2
)

Lowest Sale

=MINIFS(
SalesData[TotalSales],
SalesData[Region],
A2
)

Copy these formulas down for the remaining regions.

21. Create an Interactive Sales KPI Panel

Create dropdowns for Region, Product, Salesperson and Status. Use those selections to calculate dynamic KPIs.

Suggested Criteria Cells

Cell Criteria
B2 Region
B3 Product
B4 Salesperson
B5 Status

Dynamic Total Sales

=SUMIFS(
SalesData[TotalSales],
SalesData[Region],
B2,
SalesData[Product],
B3,
SalesData[Salesperson],
B4,
SalesData[Status],
B5
)

Dynamic Order Count

=COUNTIFS(
SalesData[Region],
B2,
SalesData[Product],
B3,
SalesData[Salesperson],
B4,
SalesData[Status],
B5
)

Dynamic Average Sale

=IFERROR(
AVERAGEIFS(
SalesData[TotalSales],
SalesData[Region],
B2,
SalesData[Product],
B3,
SalesData[Salesperson],
B4,
SalesData[Status],
B5
),
0
)

Dynamic Highest Sale

=MAXIFS(
SalesData[TotalSales],
SalesData[Region],
B2,
SalesData[Product],
B3,
SalesData[Salesperson],
B4,
SalesData[Status],
B5
)

22. Common Formula Errors

Using SUMIF Argument Order in SUMIFS

The sum range must come first in SUMIFS.

=SUMIFS(
SalesData[TotalSales],
SalesData[Region],
"East"
)

Unequal Range Sizes

Every criteria range and calculation range should contain the same number of rows.

Missing Quotation Marks

Text criteria must be placed inside quotation marks.

=COUNTIF(SalesData[Status],"Completed")

Incorrect Comparison with a Cell

This formula is incorrect:

=COUNTIF(SalesData[TotalSales],">B2")

The correct formula joins the operator and cell reference:

=COUNTIF(SalesData[TotalSales],">"&B2)

Dates Stored as Text

Date-based formulas may return incorrect results when dates are stored as text. Ensure that Excel recognizes the values as real dates.

No Matching Values in AVERAGEIFS

AVERAGEIFS may return #DIV/0! when no record satisfies the conditions. Use IFERROR when a zero or custom message is appropriate.

=IFERROR(
AVERAGEIFS(
SalesData[TotalSales],
SalesData[Region],
B2
),
"No Matching Records"
)

23. Best Practices for Conditional Formulas

  • Convert source data into an Excel Table.
  • Use structured references instead of fixed cell ranges.
  • Store criteria in separate cells to create dynamic reports.
  • Use dropdown lists to prevent spelling differences.
  • Check whether dates are stored as real Excel dates.
  • Keep all criteria ranges equal in size.
  • Use SUMIFS when more than one condition is required.
  • Use SUMIF for a simple single-condition calculation.
  • Use quotation marks around text and operators.
  • Join comparison operators to cell references using &.
  • Use IFERROR only when an empty result is expected.
  • Test formulas using both matching and nonmatching criteria.

24. Practical Assignment: Interactive Sales Summary

Create an interactive sales-summary report using at least 100 transaction records.

Required Worksheets

  • Sales_Data
  • Region_Summary
  • Product_Summary
  • KPI_Report

Required KPIs

  • Total Sales
  • Total Completed Sales
  • Number of Orders
  • Average Order Value
  • Highest Sale
  • Lowest Sale
  • Completed Orders
  • Pending Orders
  • Cancelled Orders

Assignment Tasks

  1. Convert the sales data into an Excel Table.
  2. Name the table SalesData.
  3. Create a region-wise summary using SUMIF and COUNTIF.
  4. Create a product-wise summary using SUMIFS and COUNTIFS.
  5. Calculate the average sale for every region.
  6. Calculate the highest and lowest sale for every salesperson.
  7. Create start-date and end-date criteria.
  8. Calculate sales between the selected dates.
  9. Create Region, Product and Status dropdowns.
  10. Build a dynamic KPI panel using the dropdown selections.
  11. Apply conditional formatting to highlight the highest-performing region.
  12. Create a column chart using the region-wise summary.

25. Practice Exercises

Exercise 1: Department Salary Report

Create an employee dataset and calculate total salary, average salary, highest salary and lowest salary for every department.

Exercise 2: Student Attendance Report

Count the number of students whose attendance is at least 75% and marks are at least 50.

=COUNTIFS(
StudentData[Attendance],
">=75%",
StudentData[Marks],
">=50"
)

Exercise 3: Inventory Report

Count products with stock below 20 and calculate their total inventory value.

Exercise 4: Monthly Sales Report

Calculate sales between the first and last date of a selected month.

=SUMIFS(
SalesData[TotalSales],
SalesData[OrderDate],
">="&EOMONTH(B2,-1)+1,
SalesData[OrderDate],
"<="&EOMONTH(B2,0)
)

Exercise 5: Multi-Condition Sales Report

Calculate completed computer sales in the East region for a selected date range.

=SUMIFS(
SalesData[TotalSales],
SalesData[Region],
"East",
SalesData[Category],
"Computer",
SalesData[Status],
"Completed",
SalesData[OrderDate],
">="&B2,
SalesData[OrderDate],
"<="&B3
)

26. Chapter Quiz

  1. What is conditional aggregation?
  2. What is the difference between SUMIF and SUMIFS?
  3. Where is the sum range placed in SUMIFS?
  4. When should COUNTIFS be used instead of COUNTIF?
  5. How do you join a comparison operator to a cell reference?
  6. What does the asterisk wildcard represent?
  7. How can you count blank cells?
  8. How can you calculate values between two dates?
  9. What is the purpose of MAXIFS?
  10. What is the purpose of MINIFS?
  11. How can SUMIFS perform OR-style calculations?
  12. Why should criteria ranges have equal dimensions?

27. Frequently Asked Questions

What is the main difference between SUMIF and SUMIFS?

SUMIF is designed primarily for one condition, while SUMIFS can evaluate multiple conditions. Their argument order is also different.

Does SUMIFS use AND or OR logic?

SUMIFS uses AND logic. Every supplied condition must be satisfied. To create OR logic, add separate SUMIFS formulas or use a supported array constant.

Can SUMIFS calculate values between two dates?

Yes. Use the same date column twice—once with a greater-than-or-equal-to start date and once with a less-than-or-equal-to end date.

Can COUNTIF count partial text?

Yes. Use wildcards such as * for any number of characters and ? for one character.

Why does AVERAGEIFS return #DIV/0!?

This usually happens when no records satisfy the selected conditions. Check the criteria or use IFERROR if an empty result is acceptable.

Can MAXIFS return the name associated with the highest value?

MAXIFS returns only the maximum numerical value. Combine it with XLOOKUP or INDEX and MATCH to return the related product, employee or salesperson.

Conclusion

Conditional aggregation functions turn raw transaction data into useful business information. SUMIF and SUMIFS calculate conditional totals, COUNTIF and COUNTIFS count qualifying records, while AVERAGEIF and AVERAGEIFS calculate conditional averages.

MAXIFS and MINIFS identify the highest and lowest values within specific categories. By combining these functions with Excel Tables, dropdowns and date criteria, you can create interactive sales, employee, inventory and management reports.

In the next chapter, we will study dynamic array functions, including FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, TAKE, DROP, CHOOSECOLS and CHOOSEROWS.

Leave a Reply

Your email address will not be published. Required fields are marked *