Excel Advanced - Working with Functions

All files are generated from the JSON dataset embedded in this page. The Excel-compatible file includes Practice Data, a Summary Template and a Formula Guide.

Learning outcomes

Use COUNTIF and COUNTIFS to count matching records.

Use SUMIF and SUMIFS to total selected sales.

Use AVERAGEIF and AVERAGEIFS to compare order values.

Build reliable text, number, date and wildcard criteria.

Scenario and workbook setup

You are preparing a performance report for Meridian Retail Malaysia. The source table contains 24 orders from January to March 2026.

Download and open the Excel file.
Open Practice Data. The headers occupy row 1 and the data occupies rows 2–25.
Select any cell and press Ctrl+T if you want to convert it to an Excel Table. This tutorial uses ordinary ranges for compatibility.
Open Summary Template and enter the formulas shown below.

Column map: A Order ID · B Order Date · C Region · D Salesperson · E Product · F Channel · G Units · H Unit Price · I Sales · J Status

Dataset preview

Order ID	Date	Region	Salesperson	Product	Channel	Units	Unit Price	Sales	Status

Showing the first eight of 24 records. Use the download buttons for the complete dataset.

Part A — Count matching orders

Count one text criterion with COUNTIF

How many orders came from the Central region?

=COUNTIF('Practice Data'!$C$2:$C$25,A2)

Enter Central in A2. The result should be 6. The criteria cell makes the formula reusable for other regions.

Count several criteria with COUNTIFS

How many Central orders were completed?

=COUNTIFS('Practice Data'!$C$2:$C$25,A2,'Practice Data'!$J$2:$J$25,"Completed")

Checkpoint: Central + Completed = 5 orders.

Use a wildcard

Count products whose names begin with “Smart”. The asterisk represents any sequence of characters.

=COUNTIF('Practice Data'!$E$2:$E$25,"Smart*")

Expected result: 9 orders (Smartphone and Smartwatch).

Part B — Calculate conditional totals

Total sales for one region with SUMIF

=SUMIF('Practice Data'!$C$2:$C$25,A2,'Practice Data'!$I$2:$I$25)

For Central, the result should be RM 23,817.00.

Total completed regional sales with SUMIFS

=SUMIFS('Practice Data'!$I$2:$I$25,'Practice Data'!$C$2:$C$25,A2,'Practice Data'!$J$2:$J$25,"Completed")

Central completed sales should equal RM 21,917.00. Notice that the sum range comes first in SUMIFS.

Use comparison operators

Total completed orders worth RM3,000 or more.

=SUMIFS('Practice Data'!$I$2:$I$25,'Practice Data'!$I$2:$I$25,">=3000",'Practice Data'!$J$2:$J$25,"Completed")

Checkpoint: RM 79,817.00.

Part C — Calculate conditional averages

Average one category with AVERAGEIF

Find the average value of Online orders.

=AVERAGEIF('Practice Data'!$F$2:$F$25,"Online",'Practice Data'!$I$2:$I$25)

Expected result: RM 4,061.11.

Average with multiple conditions

Find the average completed Southern order.

=AVERAGEIFS('Practice Data'!$I$2:$I$25,'Practice Data'!$C$2:$C$25,"Southern",'Practice Data'!$J$2:$J$25,"Completed")

Checkpoint: RM 3,912.50.

Part D — Date-range criteria

Excel stores dates as numbers, so date criteria should be built with DATE or joined to date cells with &.

Total Northern sales during March 2026:

=SUMIFS('Practice Data'!$I$2:$I$25,'Practice Data'!$C$2:$C$25,"Northern",'Practice Data'!$B$2:$B$25,">="&DATE(2026,3,1),'Practice Data'!$B$2:$B$25,"<="&DATE(2026,3,31))

If B2 contains the start date and C2 contains the end date:

=SUMIFS('Practice Data'!$I$2:$I$25,'Practice Data'!$C$2:$C$25,A2,'Practice Data'!$B$2:$B$25,">="&B2,'Practice Data'!$B$2:$B$25,"<="&C2)

Checkpoint: Northern sales in March = RM 17,300.00.

Regional performance summary

Type the four regions in A2:A5 and fill each formula down. The completed summary should match these control values.

Region	Orders	Total Sales	Average Sale	Completed Orders
Grand total			—

B2: =COUNTIF('Practice Data'!$C$2:$C$25,A2)
C2: =SUMIF('Practice Data'!$C$2:$C$25,A2,'Practice Data'!$I$2:$I$25)
D2: =AVERAGEIF('Practice Data'!$C$2:$C$25,A2,'Practice Data'!$I$2:$I$25)
E2: =COUNTIFS('Practice Data'!$C$2:$C$25,A2,'Practice Data'!$J$2:$J$25,"Completed")

Troubleshooting criteria ranges

Problem	Likely cause	Correction
`#VALUE!` in SUMIFS	Ranges have different sizes.	Make every criteria range and the sum range cover rows 2:25.
Date result is zero	The source dates are text, or the criterion is an unrecognised text date.	Use real Excel dates and `DATE(2026,3,1)`.
Text does not match	Extra spaces or spelling differences.	Inspect the source; clean with TRIM when necessary.
`#DIV/0!` in AVERAGEIFS	No numeric records meet all criteria.	Check criteria or use `IFERROR(formula,"No match")`.
Copied result does not change	The criteria cell was locked accidentally.	Use `A2` for the changing criterion, but lock source ranges.

Range rule: In COUNTIFS, SUMIFS and AVERAGEIFS, every criteria range must have the same number of rows and columns.

Knowledge check

1. Which function totals sales using both Region and Status?

SUMIFS

SUMIF handles one criterion; SUMIFS handles two or more.

2. Which wildcard matches exactly one character?

A question mark (?)

An asterisk (*) matches any sequence of characters.

3. Why is “>=” joined to a date cell with &?

It combines the comparison operator and the cell value into one criterion.

4. What must be true of all ranges in AVERAGEIFS?

They must have matching dimensions.

Challenge activity

Without copying a completed formula, calculate:

The number of Online orders in the Eastern region.
Total Laptop sales from completed orders.
The average order value between 1 February and 28 February 2026.
The number of salespeople whose names begin with “N”.

Reveal challenge answers

1. 3 · 2. RM 23,020.00 · 3. RM 4,200.00 · 4. 6