EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Maths Function in Excel SUBTOTAL in Excel
Secondary Sidebar
Excel Functions
  • Maths Function in Excel
    • Calculating Investment Return In Excel
    • Sumif Function Examples
    • MROUND in Excel
    • SUM Cells in Excel
    • MMULT in Excel
    • MINVERSE in Excel
    • Trunc in Excel
    • SUMIF with Text
    • COS Function in Excel
    • RANDBETWEEN in Excel
    • Excel SUMIF with OR
    • Excel DEGREES Function
    • Calculations in Excel
    • Sumif Between Two Dates
    • Calculate Income Tax in Excel
    • PRODUCT Function in Excel
    • How to Sum Multiple Rows in Excel
    • Matrix in Excel
    • ROUND Formula in Excel
    • How to SUM in Excel
    • SUMPRODUCT Formula in Excel
    • Excel Running Total
    • Excel Rounding
    • Excel Exponents
    • Excel SIN Function
    • Excel Square Root Function
    • Excel ROUNDUP Function
    • ROUND Excel Function
    • Excel ROUNDDOWN Function
    • ODD Excel Function
    • SUMPRODUCT Function in Excel
    • AGGREGATE Function in Excel
    • ABS Function in Excel
    • Excel TAN Function
    • COMBIN Function in Excel
    • CEILING Function in Excel
    • INT Excel Function
    • SUBTOTAL Function in Excel
    • LN Function in Excel
    • Excel RAND Function
    • TANH Function in Excel
    • SUMIF Function in Excel
    • SUM Function in Excel
    • POWER Function in Excel
    • EVEN Function in Excel
    • LOG Function in Excel
    • FLOOR Excel Function
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup and Reference Functions in Excel (35+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Online EXCEL ADVANCED Certification
  • Excel for HR Training

SUBTOTAL in Excel

By Pradeep SPradeep S

SUBTOTAL Function in Excel

Excel SUBTOTAL Function (Table of contents)

  • SUBTOTAL in Excel
  • SUBTOTAL Formula in Excel
  • How to Use SUBTOTAL Function in Excel?

SUBTOTAL in Excel

  • A subtotal function is an inbuilt function in excel that is categorized under the math/trig function.
  • Subtotal will not just total the numbers in a defined range of cells; it will help out by performing different arithmetic operations.
  • SUBTOTAL is versatile. You can use it to average, sum up, count a bunch of cells & other calculations.

Definition

Returns a subtotal in a database or list, i.e., finding out the subtotal of a given range of cells. It performs a specified calculation for a given set of values.

SUBTOTAL Formula in Excel

The formula for the SUBTOTAL function is as follows:

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

SUBTOTAL Formula

SUBTOTAL(function_num, ref1, [ref2],…)Where

Function_num or operation code: It indicates which function should be used to calculate the subtotals within a given list. It is a type of mathematical operation that you like to perform.

A function_num argument is applied based on the below mentioned two criteria or methods.

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,813 ratings)
  1. Include hidden values When the function_num argument is between 1-11, the SUBTOTAL function will include the hidden values.
  2. Ignore hidden values When the function_num argument is between 101-111, the SUBTOTAL function will exclude or ignore the hidden values. It will find values only invisible rows that ignore hidden values in the calculation.
Fun_num
Includes hidden values
Fun_num
Excludes hidden values
Function Description
1 101 AVERAGE It calculates the Average of a specified range.
2 102 COUNT It will Count the number of cells that contain numeric values.
3 103 COUNTA It will Counts non-empty cells in the specified range.
4 104 MAX It will find out the largest value in the specified range.
5 105 MIN It will find out the smallest value in the specified range.
6 106 PRODUCT It calculates the product of cells in the specified range.
7 107 STDEV It estimates Standard Deviation in the specified range.
8 108 STDEVP It calculates Standard Deviation based on the entire population.
9 109 SUM It calculates the Sum of the specified range.
10 110 VAR It estimates variance in the specified range.
11 111 VARP It estimates variance based on the entire population.

ref1, [ref2]: One or more references to cells containing the values that the calculation is to be performed for subtotal.

How to Use SUBTOTAL Function in Excel?

Let’s look out how the SUBTOTAL function works in Excel.

You can download this SUBTOTAL Function Excel Template here – SUBTOTAL Function Excel Template

Example #1 – SUBTOTAL Formula for values Hidden using Excel Autofilter or Rows Hidden by a filter

The table contains Quarterly Sales Data in the below-mentioned example, where individual quarters are in column E & sales data in column F.

Quarterly Sales Data

Sum function is applied in cell “F16” i.e. =SUM(F4:F15).

Sum function

It returns or results in a value of 1184.

SUBTOTAL Example 1-1

SUM function returns the same value. i.e. 1184, instead of 194 for Q4 data, if rows are hidden by a filter (Below mentioned screenshot).

SUBTOTAL Example 1-3

If I want the Correct SUM value of Q4 data only, then I need to use the SUBTOTAL function.

Before applying the SUBTOTAL function, Under the data toolbar, apply a filter for the header row.

Let’s calculate specifically Q1 or Q2 or Q3 or Q4 sales data individually for filtered data.

Use 109 as a function_num argument in SUBTOTAL function to Sum up the data. i.e. =SUBTOTAL(109,C17:C28)

SUBTOTAL Example 1-4

The SUBTOTAL function ignores rows hidden by a filter and calculates the correct result.

Here subtotal value changes dynamically according to the filter; it will find a sum of values in visible rows only; the SUBTOTAL function neglects hidden rows. (Below mentioned, e.g.)

Q4

Result For Q4

Q3

Result For Q3

Q2

Result For Q2

Q1

Result For Q1

Example #2 – SUBTOTAL Function for manually Hidden Rows

In the excel sheet, row no 15, 16, 19, and 20 have been hidden using the row formatting option by highlighting these rows, right-clicking with the mouse, and selecting Hide.

SUBTOTAL Function for manually Hidden Rows

A subtotal function is used in cell C23 of the spreadsheet to calculate the sum of visible cells.

SUBTOTAL Example 2-1

We have to Use 109 as a function_num argument in the SUBTOTAL function to Sum up the data.

i.e. =SUBTOTAL(109,C14:C21)

SUBTOTAL function to Sum up the data

Here, the SUBTOTAL function ignores manually rows hidden and calculates the correct result.

SUBTOTAL Example 2-3

It will find a sum of values in visible rows only; the SUBTOTAL function neglects hidden rowsExample #3 – SUBTOTAL Function for normal value

As all of the cells in the range of quarterly sales figures are visible, none of them is filtered, or rows are hidden; it includes all the values.

SUBTOTAL Example 3

In the below-mentioned example, multiple subtotal functions are applied.

In cell, C14, C15 & C16 SUBTOTAL function is applied.

SUBTOTAL function is applied

To calculate SUM value, Function number 9 or 109 is used in a subtotal function where It calculates the Sum of a specified range, i.e. =SUBTOTAL (9, C4:C11)

SUBTOTAL Function

It will give us the sum of all values in C4:C11, provided none are filtered & rows are hidden in that specified range.

SUBTOTAL Example 3-3

To calculate the MIN value, Function number 5 or 105 is used in a subtotal function where It will find out the smallest value in the specified range. In a cell, C15, a SUBTOTAL function is applied, i.e. =SUBTOTAL(5,C4:C11)

SUBTOTAL function

It will find out the smallest value in the specified range, i.e. C4:C11

Smallest value

To calculate the MAX value, Function number 4 or 104 is used in a subtotal function where It will find out the largest value in the specified range. In a cell, C16, a SUBTOTAL function is applied, i.e. =SUBTOTAL(4,C4:C11)

SUBTOTAL function

It will find out the largest value in the specified range, i.e. C4:C11.

SUBTOTAL Example 3-7

Things to Remember about the SUBTOTAL Function

  • A SUBTOTAL function can be applied to find a sum of filtered values.
  • A SUBTOTAL function is used to ignore values in hidden rows.
  • Function _ num argument value should not be greater than 11 or less than 1, similarly should not be greater than 111 or less than 101; otherwise, it will give #VALUE! error.
  • A SUBTOTAL function is applicable only for the numeric value.
  • If there are any subtotals present in the SUBTOTAL range, they will be neglected, i.e. If any already existing SUBTOTAL formulas are present in the input range of another SUBTOTAL formula, then these values are neglected by subtotal function during the calculation so that double counting is avoided & it prevents the error of double counting.
  • A SUBTOTAL function can be used to dynamically summarize data.
  • Blank cells and cells containing non-numeric values are ignored by subtotal function during the calculation.

Recommended Articles

This has been a guide to SUBTOTAL. Here we discuss the SUBTOTAL Formula and how to use the SUBTOTAL Function along with excel examples and downloadable excel templates. You may also look at these useful functions in excel –

  1. FIND Function in Excel
  2. NOT in Excel
  3. TRANSPOSE in Excel
  4. VBA SUB
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel for HR Training (8 Courses, 10+ Projects)4.9
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formulas, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download SUBTOTAL Function Excel Template

EDUCBA

Download SUBTOTAL Function Excel Template

EDUCBA

डाउनलोड SUBTOTAL Function Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more