Subtotals in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Calculate subtotal ignoring hidden rows

You have a list of sales amounts in cells A2:A10. Some rows are hidden. Which formula correctly calculates the subtotal of visible sales only?

A =SUM(A2:A10)

B =SUBTOTAL(109, A2:A10)

C =SUBTOTAL(9, A2:A10)

D =SUMIF(A2:A10, ">0")

💡 Hint

Use SUBTOTAL with function number that ignores hidden rows.

The SUBTOTAL function with function number 109 sums only visible cells, ignoring filtered rows. Option B uses 9 which includes manually hidden rows. SUM sums all cells regardless of visibility. SUMIF filters by value, not visibility.

❓ Function Choice

intermediate

Choose the correct SUBTOTAL function number for AVERAGE

You want to calculate the average of visible cells in B2:B20, ignoring manually hidden rows. Which function number should you use with SUBTOTAL?

A 101

B 1

C 2

D 102

💡 Hint

Function numbers 101-111 ignore filtered rows.

Function number 101 calculates AVERAGE ignoring filtered rows. Numbers 1-11 include manually hidden rows. Numbers 102 and 2 are for COUNT functions.

🎯 Scenario

advanced

Subtotal with multiple columns and filters

You have a table with columns: Region (A), Sales (B), and Quantity (C). You apply a filter to show only 'East' region rows. Which formula correctly sums the visible Sales values?

A =SUBTOTAL(109, B2:B100)

B =SUM(B2:B100)

C =SUMIF(A2:A100, "East", B2:B100)

D =SUBTOTAL(9, B2:B100)

💡 Hint

Filters hide rows but SUBTOTAL with 109 ignores filtered rows.

SUBTOTAL with function number 109 sums only visible cells, so it respects filters. SUM sums all cells ignoring filters. SUMIF sums based on criteria but does not consider filtered visibility.

📊 Formula Result

advanced

Effect of nested SUBTOTAL functions

Given the formula =SUBTOTAL(9, A2:A10) + SUBTOTAL(9, A11:A20), what happens if you replace the range with =SUBTOTAL(9, A2:A20)?

A The result is double the sum of A2:A20.

B The formula returns a #VALUE! error.

C The result is the same as adding the two subtotals.

D The result is half the sum of A2:A20.

💡 Hint

SUBTOTAL sums visible cells in the given range.

SUBTOTAL sums visible cells in the specified range. Summing two non-overlapping ranges separately or summing the combined range gives the same total.

❓ data_analysis

expert

Identify error in subtotal formula with filtered data

You want to calculate the average price in column C for visible rows only after applying filters. You use =SUBTOTAL(1, C2:C50). After filtering, the average seems incorrect. What is the likely cause?

A You need to use SUM instead of SUBTOTAL.

B The range C2:C50 includes blank cells causing error.

C SUBTOTAL does not work with filtered data.

D You used function number 1 which includes manually hidden rows, use 101 instead.

💡 Hint

Function numbers 1-11 include manually hidden rows, 101-111 exclude filtered rows.

Function number 1 calculates average including manually hidden rows. To exclude filtered rows, use 101.