SUBTOTAL function is use to get a subtotal in a list or from database. SUBTOTAL function can either include or exclude values in manually hidden rows. By default, SUBTOTAL excludes values in rows hidden by a filter. The SUBTOTAL function automatically ignores other SUBTOTAL formulas that exist in references to prevent double-counting.
Syntax:-=SUBTOTAL (function_num, ref1, [ref2], …)
Arguments
- function_num – A number that specifies which function to use in calculating subtotals within a list. See table below for full list.
- ref1 – A named range or reference to subtotal.
- ref2 – [optional] A named range or reference to subtotal.
There are 11 functions available, each with two options, as seen in the table below. When function_num is between 1-11, SUBTOTAL includes cells that have been manually hidden. When function_num is between 101-111, SUBTOTAL excludes values in rows that have been manually hidden and gives the Result based on visible data. This can be more clear with below table.
| Function name | Include hidden value | Ignore hidden value |
| AVERAGE | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUCT | 6 | 106 |
| STDEV | 7 | 107 |
| STDEVP | 8 | 108 |
| SUM | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
Let’s understand With All Example:-
Suppose You have the details of the amount earned in year-2022. Now you need month Wise Average of the amount earned. So follow the steps below.
- Select data with Headers.
- Data Tab > subtotal
- A subtotal dialog box will open.
- Select Month in At each change in.
- Select Average in Use function
- Select amount in add subtotal to
- Click on Summary Below Data to view the grand total at the end of the selected data.
- Click ok
- Subtotal with Average
The average function considers both visible data and hidden data to be mandatory. But this problem can be avoided by using the average function with the subtotal. The subtotal gives you a choice of whether or not to count the hidden data. Using subtotal you can subtract the hidden data from the average as per your requirement and get the average of the visible data only.
Syntax:
- =subtotal(1,range) – To get the average Include hidden data.
- =subtotal(101,range) – To get the average of the visible data only.
2. Subtotal with Count
The COUNT function counts the number of cells that contain numbers/ numeric value. But The COUNT function count both visible data and hidden data to be mandatory. This problem can be avoided by using the COUNT function with the subtotal. The subtotal gives you a choice of whether or not to count the hidden data. Using subtotal you can subtract the hidden data as per your requirement and get the number of cells that contain numerical value of the visible data only.
Syntax:
- =subtotal(2,range) – To get the number of cells that contain numeric value in the range Include hidden data.
- =subtotal(102, range) – To get the number of cells that contain numeric value of only visible data in the range.
3. Subtotal with Counta
The COUNTA function count the number of cells that are not empty in the range. But The COUNTA function count both visible data and hidden data to be mandatory. This problem can be avoided by using the COUNTA function with the subtotal. The subtotal gives you a choice of whether or not to count the hidden data. Using subtotal you can subtract the hidden data as per your requirement and get the number of filled cells of the visible data only.
Syntax:
- =subtotal(3,range) – To get the number of filled cells in the range Include hidden data.
- =subtotal(103, range) – To get the number of filled cells of only visible data in the range.
4. Subtotal with Max
The Max Function is used to get the largest numeric value in the selected data. but The max function count both visible data and hidden data to be mandatory. But this problem can be avoided by using the max function with the subtotal. The subtotal gives you a choice of whether or not to count the hidden data. Using subtotal you can subtract the hidden data as per your requirement and get the largest numeric value of the visible data only.
Syntax:
- =subtotal(4,range) – To get the largest numeric value Include hidden data.
- =subtotal(104, range) – To get the largest numeric value of the visible data only.
5. Subtotal with MIN
The Min Function is used to get the smallest numeric value in the selected data. but The min function count both visible data and hidden data to be mandatory. But this problem can be avoided by using the min function with the subtotal. The subtotal gives you a choice of whether or not to count the hidden data. Using subtotal you can subtract the hidden data as per your requirement and get the smallest numeric value of the visible data only.
Syntax:
- =subtotal(5,range) – To get the smallest numeric value Include hidden data.
- =subtotal(105, range) – To get the smallest numeric value of the visible data only.
6. Subtotal with Product
The PRODUCT function multiplies all the numbers given as arguments. But The product function count both visible data and hidden data to be mandatory. So, This problem can be avoided by using the product function with the subtotal. The subtotal gives you a choice of whether or not to count the hidden data. Using subtotal you can subtract the hidden data and get the product value of the visible data only.
Syntax:
- =subtotal(6,range) – To get the product value Include hidden data.
- =subtotal(106, range) – To get the product value of the visible data only.
7. Subtotal with STDEV
STDEV (Standard deviation) Function returns a number that tells you how far numbers are from their mean (average). But The STDEV function count both visible data and hidden data to be mandatory. But this problem can be avoided by using the max function with the subtotal. The subtotal gives you a choice of whether or not to count the hidden data. Using subtotal you can subtract the manually hidden data as per your requirement and get the Standard deviation of the visible data only.
Syntax:
- =subtotal(7,range) – To get the Standard deviation Include hidden data.
- =subtotal(107, range) – To get the Standard deviation of the visible data only.
9. Subtotal with SUM
The sum formula is used to get the sum of the selected data. but The Sum formula count both visible data and hidden data to be mandatory. But this problem can be avoided by using the sum formula with the subtotal. The subtotal gives you a choice of whether or not to count the hidden data. Using subtotal you can subtract the hidden data from the sum(total) as per your requirement and get the sum(total) of the visible data only.
Syntax:
- =subtotal(9,range) – To get the total Include hidden data.
- =subtotal(109, range) – To get the total of the visible data only.
