Fresh Excel Tips

The SUBTOTAL function can be used to calculate subtotals in a list or database. There are different subtotal operations available that are all covered by just one function. The syntax is provided here.

How To use the SUBTOTAL function for a number of calculations:

1. Copy the data shown in columns A and B in the image below.
2. Select cells C2:C10 and enter the daily sales of each team.
3. Calculate the average in cell F2 with the following formula: =SUBTOTAL(1,$C$2:$C$10).
4. Calculate the sum in cell F3 with the following formula: =SUBTOTAL(9,$C$2:$C$10).
5. Calculate the lowest sales value in cell F4 with the following formula: =SUBTOTAL(5,$C$2:$C$10).
6. Calculate the maximum value in cell F5 with the following formula: =SUBTOTAL(4,$C$2:$C$10).

This tip is compatible with Excel 97, 2000, 2003 and 2007.

1. How to Use the SUBTOTAL function to sum a filtered list in Excel
2. How to Use the SUBTOTAL function to count rows in filtered lists
3. How to Use the MEDIAN function to calculate the median sales
4. Use the SUMPRODUCT function to multiply and sum at once
5. How to Determine the average output in Excel