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.

Practice

1. What does the SUBTOTAL function do in Excel?

easy

A. Calculates sums, averages, counts, and other operations on filtered or grouped data

B. Sorts data alphabetically or numerically

C. Removes duplicates from a list

D. Creates charts from selected data

Solution

1. Step 1: Understand the purpose of SUBTOTAL

   The SUBTOTAL function is designed to perform calculations like sum, average, or count on a range of data, especially useful when data is filtered or grouped.

2. Step 2: Compare with other options

   Sorting, removing duplicates, and creating charts are different Excel features unrelated to SUBTOTAL.

3. Final Answer:

   Calculates sums, averages, counts, and other operations on filtered or grouped data -> Option A

4. Quick Check:

   SUBTOTAL = Group calculations [OK]

Hint: SUBTOTAL works on filtered data for accurate group totals [OK]

Common Mistakes:

• Confusing SUBTOTAL with sorting or filtering
• Thinking SUBTOTAL removes duplicates
• Assuming SUBTOTAL creates charts

2. Which of the following is the correct syntax for the SUBTOTAL function to sum values in range A2:A10?

easy

A. =SUBTOTAL(1, A2:A10)

B. =SUBTOTAL(109, A2:A10)

C. =SUBTOTAL(9, A2:A10)

D. =SUBTOTAL(10, A2:A10)

Solution

1. Step 1: Identify function number for SUM

   In SUBTOTAL, function number 9 means SUM including hidden rows, while 109 means SUM ignoring hidden rows.

2. Step 2: Choose correct syntax for basic sum

   Since the question does not specify ignoring hidden rows, 9 is the standard function number for SUM.

3. Final Answer:

   =SUBTOTAL(9, A2:A10) -> Option C

4. Quick Check:

   SUM function number = 9 [OK]

Hint: Use 9 for SUM in SUBTOTAL unless ignoring hidden rows [OK]

Common Mistakes:

• Using 1 which is for AVERAGE
• Using 10 which is for COUNT
• Confusing 9 and 109 without context

3. Given the data in cells A2:A6 as {10, 20, 30, 40, 50} and rows 4 and 5 are manually hidden, what is the result of =SUBTOTAL(9, A2:A6)?

medium

A. 90

B. 60

C. 100

D. 150

Solution

1. Step 1: Understand SUBTOTAL with function number 9

   Function 9 sums visible and hidden rows, so it includes all values including manually hidden rows.

2. Step 2: Calculate sum of all values

   Sum of 10+20+30+40+50 = 150, but since rows 4 and 5 (values 40 and 50) are manually hidden, SUBTOTAL(9, ...) still includes them.

3. Step 3: Reconsider filtering effect

   Actually, SUBTOTAL with 9 includes manually hidden rows, so sum is 150. But question says rows 4 and 5 are manually hidden. SUBTOTAL(9, ...) includes hidden rows, so sum is 150.

4. Step 4: Check options

   150 is the total sum. 60 is sum of visible rows only (10+20+30=60).

5. Final Answer:

   150 -> Option D

6. Quick Check:

   SUBTOTAL(9) sums all rows including hidden [OK]

Hint: Function 9 sums all rows, even hidden ones [OK]

Common Mistakes:

• Assuming manually hidden rows are excluded with function 9
• Confusing function 9 with 109
• Adding only visible rows mistakenly

4. You want to sum only visible rows in range B2:B10, but your formula =SUBTOTAL(9, B2:B10) includes hidden rows. How do you fix it?

medium

A. Add a filter to the data

B. Change function number to 109: =SUBTOTAL(109, B2:B10)

C. Use =SUM(B2:B10) instead

D. Use =SUBTOTAL(1, B2:B10)

Solution

1. Step 1: Understand difference between function numbers 9 and 109

   Function 9 sums all rows including hidden, while 109 sums only visible rows, ignoring manually hidden rows.

2. Step 2: Fix formula to sum visible rows only

   Change function number from 9 to 109 to exclude hidden rows in the sum.

3. Final Answer:

   Change function number to 109: =SUBTOTAL(109, B2:B10) -> Option B

4. Quick Check:

   Use 109 to sum visible rows only [OK]

Hint: Use 109 to exclude hidden rows in SUBTOTAL sum [OK]

Common Mistakes:

• Using SUM which ignores filtering
• Using function number 1 which is AVERAGE
• Thinking adding a filter fixes formula automatically

5. You have sales data in column C with some rows filtered out and some manually hidden. You want to calculate the average sales of only visible rows and also count how many visible sales entries there are. Which formulas should you use?

hard

A. =SUBTOTAL(101, C2:C100) for average and =SUBTOTAL(103, C2:C100) for count

B. =SUBTOTAL(1, C2:C100) for average and =SUBTOTAL(2, C2:C100) for count

C. =AVERAGE(C2:C100) and =COUNT(C2:C100)

D. =SUBTOTAL(109, C2:C100) for average and =SUBTOTAL(110, C2:C100) for count

Solution

1. Step 1: Identify function numbers for average and count excluding hidden rows

   Function numbers 101 and 103 calculate average and count respectively, ignoring hidden rows.

2. Step 2: Confirm formulas for visible rows only

   Using =SUBTOTAL(101, C2:C100) gives average of visible rows; =SUBTOTAL(103, C2:C100) counts visible numeric entries.

3. Final Answer:

   =SUBTOTAL(101, C2:C100) for average and =SUBTOTAL(103, C2:C100) for count -> Option A

4. Quick Check:

   101 = average visible, 103 = count visible [OK]

Hint: Use 101 for average and 103 for count on visible rows [OK]

Common Mistakes:

• Using 1 and 2 which include hidden rows
• Using AVERAGE and COUNT ignoring filters
• Confusing function numbers 109 and 110 which are for SUM and COUNT ignoring hidden rows