Excel Advanced - Function Challenges

Microsoft Excel Advanced · Lesson 02 Continuation

Advanced Function Challenges

Apply conditional functions to more demanding business questions using combined criteria, dynamic dates, wildcards, exclusions, OR logic and reconciliation.

0 of 12 completed

Before you begin

Continue with the Lesson 2 workbook. Use the same Practice Data worksheet and these source columns:

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

The data occupies rows 2–25. Put your answers on a new worksheet named Advanced Questions.

Level 1 · Precision
Questions 1–4

Level 2 · Dynamic criteria
Questions 5–8

Level 3 · Analysis
Questions 9–12

1SUMIFS · 3 criteria

Completed Eastern online sales

Calculate total sales where Region is Eastern, Channel is Online, and Status is Completed.

Mark as completed

Hint

Use Sales as the sum range, followed by three range–criterion pairs.

Worked solution

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

Expected result: RM 11,400.00

2COUNTIFS · comparison

Completed orders with at least three units

Count completed orders where Units are greater than or equal to 3.

Mark as completed

Hint

The numeric criterion must be written as text: ">=3".

Worked solution

=COUNTIFS('Practice Data'!$G$2:$G$25,">=3",'Practice Data'!$J$2:$J$25,"Completed")

Expected result: 13 orders

3COUNTIF · wildcard

Salesperson names ending in “a”

Count all order rows where the salesperson’s name ends with the letter a.

Mark as completed

Hint

Place an asterisk before the final letter: *a.

Worked solution

=COUNTIF('Practice Data'!$D$2:$D$25,"*a")

Expected result: 7 rows

4AVERAGEIFS · dates

Average completed February order

Calculate the average sales value for completed orders dated from 1–28 February 2026.

Mark as completed

Hint

Use two criteria against the same Order Date range: one lower bound and one upper bound.

Worked solution

=AVERAGEIFS('Practice Data'!$I$2:$I$25,'Practice Data'!$J$2:$J$25,"Completed",'Practice Data'!$B$2:$B$25,">="&DATE(2026,2,1),'Practice Data'!$B$2:$B$25,"<="&DATE(2026,2,28))

Expected result: RM 3,720.00

5Dynamic criteria cells

Build a reusable date-range report

Enter a Status in B2, Start Date in C2 and End Date in D2. Write one formula that totals sales using all three selections. Test it with Completed, 1 January 2026 and 31 March 2026.

Mark as completed

Hint

Join comparison operators to C2 and D2 with &. Do not put the cell references inside quotation marks.

Worked solution

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

Expected test result: RM 85,377.00

6OR logic · SUM + SUMIFS

Combine Central and Eastern

Calculate completed sales from either Central or Eastern without adding a helper column.

Mark as completed

Hint

SUMIFS applies AND logic between criteria. For OR logic, calculate the two regional SUMIFS results and add them with SUM.

Worked solution

=SUM(SUMIFS('Practice Data'!$I$2:$I$25,'Practice Data'!$C$2:$C$25,{"Central","Eastern"},'Practice Data'!$J$2:$J$25,"Completed"))

Expected result: RM 42,777.00
For older Excel versions, add two separate SUMIFS formulas.

7Exclusion criterion

Exclude cancelled orders

Calculate total sales for every order whose Status is not Cancelled.

Mark as completed

Hint

The “not equal to” operator is <>.

Worked solution

=SUMIF('Practice Data'!$J$2:$J$25,"<>Cancelled",'Practice Data'!$I$2:$I$25)

Expected result: RM 97,127.00

8Wildcard inside text

Products containing “top”

Count product names that contain the letters top anywhere in the text.

Mark as completed

Hint

Place an asterisk on both sides of the search text.

Worked solution

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

Expected result: 6 Laptop orders

9SUMIFS · compound filter

Completed online sales in March

Total sales for Online, Completed orders between 1 and 31 March 2026.

Mark as completed

Hint

This formula needs four criteria: Channel, Status, minimum date and maximum date.

Worked solution

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

Expected result: RM 10,250.00

10AVERAGEIFS · threshold

Average high-value completed order

Calculate the average Sales value among Completed orders worth at least RM4,000.

Mark as completed

Hint

Use Sales as both the average range and one criteria range.

Worked solution

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

Expected result: RM 5,605.56 across 9 matching orders.

11Audit and repair

Repair a broken SUMIFS formula

The formula below returns #VALUE!. Identify every problem and rewrite it correctly to total completed Smartphone sales where Units are at least 2.

=SUMIFS('Practice Data'!I2:I24,'Practice Data'!E2:E25,"Smartphone",'Practice Data'!J2:J25,"Complete",'Practice Data'!G2:G25,"3>=")

Mark as completed

Hint

Check range sizes, exact status spelling, and the order of the comparison operator and number.

Worked solution

The sum range ends at row 24 instead of 25; Complete should be Completed; and "3>=" should be ">=2".

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

Expected result: RM 18,450.00 across 4 orders.

12Reconciliation

Prove that the regional report is complete

Create four regional SUMIF results, then add them. Reconcile that total with a direct SUM of the Sales column. Explain what a difference would indicate.

Mark as completed

Hint

If regions are in A2:A5, enter SUMIF in B2 and fill down. In B7, calculate SUM(B2:B5); in B8, calculate the source-data total; in B9, subtract them.

Worked solution

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

B7: =SUM(B2:B5)

B8: =SUM('Practice Data'!$I$2:$I$25)

B9: =B7-B8

Regional total: RM 103,077.00 · Source total: RM 103,077.00 · Difference: RM 0.00
A non-zero difference suggests a missing, misspelled or unlisted region, or an incorrect range.

Final control totals

Measure	Control value
All orders	24
Completed orders	19
Total sales	RM 103,077.00
Completed sales	RM 85,377.00
Central + Eastern completed sales	RM 42,777.00

All 12 challenges completed

You have practised conditional counting, totals, averages, wildcards, dynamic dates, exclusions, OR logic, auditing and reconciliation.

Lesson 02 Advanced Questions · Continue to Lesson 03 when ready