DEV Community

Andrews
Andrews

Posted on • Originally published at geekexcel.com on

Excel SUBTOTAL Function in Office 365!! (*With Examples*)

#excelfunctions #excelsubtotalfunctio

Excel SUBTOTAL Function:

This article describes the explanation, basic syntax, and usage of the Excel SUBTOTAL function in Office 365. Get an official version of Microsoft Excel from the following link: https://www.microsoft.com/en-in/microsoft-365/excel.

SUBTOTAL Function – Description

  • The SUBTOTAL function returns an aggregate result in a list or database.
  • Using this function, you can summarize amounts by including or exclude hidden/filtered rows.

Syntax of SUBTOTAL Function: 

=SUBTOTAL(function\_num, ref1, [ref2],...)
Enter fullscreen mode Exit fullscreen mode

Argument Description:

  • Function_num (Required) – Function that needs to use for the subtotal.
    • Numbers from 1 to 11 – Includes manually hidden rows.
    • Numbers from 101 to 111 – Excludes manually hidden rows & filtered-out cells.

| Function_num (includes hidden values) | Function_num (Excludes hidden values) | Function |
| 1 | 101 | AVERAGE |
| 2 | 102 | COUNT |
| 3 | 103 | COUNTA |
| 4 | 104 | MAX |
| 5 | 105 | MIN |
| 6 | 106 | PRODUCT |
| 7 | 107 | STDEV |
| 8 | 108 | STDEVP |
| 9 | 109 | SUM |
| 10 | 110 | VAR |
| 11 | 111 | VARP |

  • Ref1 (Required) – First named reference or range to find the subtotal.
  • Ref2,… (Optional) – Named references or ranges 2 to 254 that you wish to find the subtotal.

Important Note:

  • The SUBTOTAL function is only for vertical ranges (columns of data) and not for horizontal ranges (rows of data).
  • This function excludes any rows that are not included in the filter result.
  • Nested subtotals are neglected to avoid double counting.
  • #VALUE! Error – If any of the references or ranges are 3-D references, the function returns the #VALUE! Error. For example, referring the same cells from Sheet 1 & Sheet 2, =SUBTOTAL(E1:E5, E1:E5) will return an error.

Practical Examples:

Let’s look at some examples of this SUBTOTAL function and explore how it works in Microsoft Excel.

Example 1: SUM using SUBTOTAL (Including & Excluding Manually Hidden Rows)

SUM Function
SUM Function

  • After applying a filter, two rows are hidden manually, the SUM Function includes the hidden row values.

SUM Function includes hidden row values
SUM Function includes hidden row values

  • Now, using the SUBTOTAL Function , we can calculate the SUM by excluding manually hidden rows. Here, the argument 109 is used for calculating SUM by excluding hidden rows.

SUBTOTAL Function - Excludes manually hidden row values
SUBTOTAL Function – Excludes manually hidden row values

  • The SUBTOTAL function can calculate the SUM by including manually hidden rows , so we have used the argument 9 for calculating SUM by including hidden rows.

SUBTOTAL Function - Includes manually hidden row values
SUBTOTAL Function – Includes manually hidden row values

Example 2: AVERAGE using SUBTOTAL

  • Using the SUBTOTAL Function, we have calculated the AVERAGE of Total Cost.

SUBTOTAL Function to calculate AVERAGE
SUBTOTAL Function to calculate AVERAGE

Verdict:

In the above short article, we have described the explanation, basic syntax, and usage of the Excel SUBTOTAL function in Office 365 with the examples. Drop your valuable queries/feedback in the below comment section. Thanks for visiting Geek Excel!! Keep Learning with us!!

Related Articles:

Top comments (0)