Excel SUBTOTAL: Advanced Formula Aggregation

How To Use The Excel SUBTOTAL

Subtotals allow you to create summary of the groups list. You can do many various calculations based on subtotals. See at the table below. That’s an example report.

To insert subtotals first you have to sort your table. Sort column in which you want to use subtotals.

Next go to the Ribbon to the Data tab. Click Subtotals button.

Dialog box appears.

You can choose here:

• For what you want to subtotal
• Which function (eg. Sum, Max, Min, Average, …)
• What you want to subtotal as a result

Excel creates an outline that you can expand with the buttons or plus and minus buttons on the left side of the page.

Level 1 – grand count for data across the table

Level 2 – count of rows for each category

Level 3 – displays the entire table with subtotals below each category

To restore the appearance of the table, click Remove All.

Template

Further reading: 
Sharing workbook
How to Count Unique Values?
Summing only negative/positive values
Basic concepts 
Getting started with Excel
Cell References