Excel-SUBTOTAL, AGGREGATE and Filtered Data Practice Questions with Solutions

Introduction

SUBTOTAL and AGGREGATE are useful Excel functions when working with filtered lists and large datasets. They can calculate totals, averages, counts, maximums, and minimums while handling hidden or filtered rows differently from regular functions. In this chapter, you will practice these functions through 10 solved examples, starting with simple filtered totals and gradually moving toward more practical data-analysis problems. Excel-SUBTOTAL, AGGREGATE and Filtered Data Practice questions with solutions to help you understand the concepts.

Q1. Calculate the Total Sales Using SUBTOTAL

Problem Statement

You have a sales table containing several transactions. Use SUBTOTAL to calculate the total sales.

Sample Data

Excel Formula

=SUBTOTAL(9,C2:C7)

Output

157000

Explanation

The syntax is:

=SUBTOTAL(function_num, ref1)

Here:

9 → SUM
C2:C7 → Sales range

So:

25000 + 30000 + 18000 + 22000 + 35000 + 27000
= 157000

Concept Covered

• SUBTOTAL
• SUM
• Basic filtered-data calculation

Q2. Calculate Average Sales Using SUBTOTAL

Problem Statement

Use SUBTOTAL to calculate the average sales from the given data.

Sample Data

Excel Formula

=SUBTOTAL(1,B2:B6)

Output

26000

Explanation

In SUBTOTAL:

1 → AVERAGE

Therefore:

(25000 + 30000 + 18000 + 22000 + 35000) / 5
= 26000

Concept Covered

• SUBTOTAL
• AVERAGE
• Function numbers

Q3. Count Visible Sales Records Using SUBTOTAL

Problem Statement

Use SUBTOTAL to count the number of numeric sales records.

Sample Data

Excel Formula

=SUBTOTAL(2,B2:B7)

Output

6

Explanation

The function number:

2 → COUNT

SUBTOTAL(2,...) counts numeric values in the reference.

This becomes particularly useful when rows are filtered.

Concept Covered

• SUBTOTAL
• COUNT
• Filtered records

Q4. Calculate Total Sales After Filtering by City

Problem Statement

Suppose you filter the sales table to show only Delhi records. Use SUBTOTAL to calculate the total of the visible sales.

Sample Data

Apply a filter to the City column and select:

Delhi

Then use:

=SUBTOTAL(9,C2:C7)

Output

78000

Explanation

After filtering, only these rows remain visible:

Therefore:

25000 + 18000 + 35000 = 78000

The important advantage is that SUBTOTAL recalculates when the filter changes.

Concept Covered

• SUBTOTAL
• Excel Filters
• Visible rows
• Dynamic calculation

Q5. Difference Between SUBTOTAL 9 and SUBTOTAL 109

Problem Statement

Understand the difference between:

=SUBTOTAL(9,B2:B7)

and:

=SUBTOTAL(109,B2:B7)

Explanation

Both perform a SUM, but they handle manually hidden rows differently.

Both ignore rows removed from the result by filtering.

Example

Suppose:

If a row is manually hidden:

=SUBTOTAL(9,B2:B5)

can include that manually hidden row.

Whereas:

=SUBTOTAL(109,B2:B5)

excludes it.

Important Point

For filtered reports where you also want manually hidden rows excluded, the 100 series is useful:

101 → AVERAGE
102 → COUNT
103 → COUNTA
104 → MAX
105 → MIN
109 → SUM

Concept Covered

• SUBTOTAL
• Hidden rows
• Filtered rows
• Function numbers 9 and 109

Q6. Find the Largest Visible Sales Value

Problem Statement

Use SUBTOTAL to find the largest visible sales value after filtering the dataset.

Sample Data

Filter the City column to show only Delhi.

Excel Formula

=SUBTOTAL(4,C2:C7)

Output

35000

Explanation

The function number:

4 → MAX

After filtering for Delhi, the visible sales values are:

25000
18000
35000

The largest value is:

35000

Concept Covered

• SUBTOTAL
• MAX
• Filtered data
• Visible values

Q7. Calculate Average of Filtered Data Using AGGREGATE

Problem Statement

Use AGGREGATE to calculate the average of a sales range while ignoring hidden rows.

Sample Data

Excel Formula

=AGGREGATE(1,5,B2:B6)

Explanation

The syntax is:

=AGGREGATE(function_num, options, array)

Here:

1 → AVERAGE
5 → Ignore hidden rows
B2:B6 → Sales range

So the formula calculates the average while ignoring hidden rows.

Concept Covered

• AGGREGATE
• AVERAGE
• Options
• Hidden rows

Q8. Find the Largest Visible Value Using AGGREGATE

Problem Statement

Use AGGREGATE to find the largest value in a dataset while ignoring hidden rows.

Sample Data

Excel Formula

=AGGREGATE(4,5,B2:B6)

Explanation

Here:

4 → MAX
5 → Ignore hidden rows

So:

=AGGREGATE(4,5,B2:B6)

returns the largest visible value while ignoring hidden rows.

If all rows are visible, the result is:

35000

Concept Covered

• AGGREGATE
• MAX
• Hidden rows
• Data analysis

Q9. Find the Smallest Visible Value Using AGGREGATE

Problem Statement

Use AGGREGATE to find the smallest visible sales value while ignoring hidden rows.

Sample Data

Excel Formula

=AGGREGATE(5,5,B2:B6)

Explanation

Here:

5 → MIN
5 → Ignore hidden rows

Therefore, the function finds the smallest visible value.

With all rows visible:

18000

Concept Covered

• AGGREGATE
• MIN
• Hidden rows
• Filtered-data analysis

Q10. Create a Filtered Sales Summary Using SUBTOTAL and AGGREGATE

Problem Statement

Create a small sales summary that changes when the data is filtered.

You need to calculate:

1. Total visible sales
2. Average visible sales
3. Number of visible sales records
4. Highest visible sale
5. Lowest visible sale

Sample Data

Assume the data is in rows 2:9.

1. Total Visible Sales

=SUBTOTAL(109,D2:D9)

2. Average Visible Sales

=SUBTOTAL(101,D2:D9)

3. Number of Visible Sales Records

=SUBTOTAL(102,D2:D9)

4. Highest Visible Sale

=SUBTOTAL(104,D2:D9)

5. Lowest Visible Sale

=SUBTOTAL(105,D2:D9)

Explanation

The summary can look like this:

Now filter the City column.

For example, select:

Delhi

The formulas automatically calculate the values for the visible Delhi records.

This is a useful technique for creating simple interactive Excel reports without manually changing the formulas every time you apply a filter.

Concepts Covered

• SUBTOTAL
• AGGREGATE
• Filters
• Visible data
• SUM
• AVERAGE
• COUNT
• MAX
• MIN

Key Takeaways

• SUBTOTAL is useful for calculations on filtered lists.
• AGGREGATE provides more calculation and error-handling options.
• SUBTOTAL(9,range) performs a conditional-looking total that responds to filters.
• SUBTOTAL(109,range) also excludes manually hidden rows.
• SUBTOTAL function numbers determine the calculation being performed.
• AGGREGATE uses both a function number and an option number.
• AGGREGATE can ignore hidden rows and/or errors depending on the selected option.
• Filtered data can be analyzed dynamically without changing the formula.
• SUBTOTAL is particularly useful for Excel tables and filtered reports.
• AGGREGATE is useful when you need more control over hidden rows, errors, and advanced calculations.
• These functions are useful for sales reports, inventory lists, employee data, financial reports, and dashboards.

FAQs

1. What is the use of SUBTOTAL in Excel?

SUBTOTAL performs calculations such as SUM, AVERAGE, COUNT, MAX, and MIN while working intelligently with filtered data.

2. Why should I use SUBTOTAL instead of SUM for filtered data?

A normal SUM can include rows that are hidden by a filter. SUBTOTAL can calculate using only the visible filtered records.

3. What is the difference between SUBTOTAL 9 and 109?

Both perform SUM. SUBTOTAL(9,...) ignores filtered-out rows but can include manually hidden rows. SUBTOTAL(109,...) also excludes manually hidden rows.

4. What is the use of AGGREGATE in Excel?

AGGREGATE performs calculations such as SUM, AVERAGE, MAX, MIN, LARGE, and SMALL while providing options to ignore hidden rows, errors, or nested calculations.

5. Can AGGREGATE ignore errors?

Yes. Depending on the selected option, AGGREGATE can ignore cells containing errors.

6. Which is better for filtered data, SUBTOTAL or AGGREGATE?

They serve different needs. SUBTOTAL is straightforward for common calculations on filtered data, while AGGREGATE provides more options, including handling errors and additional functions.

7. Does SUBTOTAL automatically change when I apply a filter?

Yes. When a filter changes which rows are visible, a SUBTOTAL formula recalculates based on the visible records.

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Rishabh Kumar