## How to Use the SUBTOTAL Function and Formula in Excel

In this tutorial, you'll learn how to use the **SUBTOTAL function** and formula in Excel.

**SUBTOTAL() **is a function to return a subtotal in a list or database.

### SUBTOTAL **Syntax**

The syntax of the **SUBTOTAL**() function is as follows:

=

SUBTOTAL(function_num,ref1,[ref2],...)

**Return value**

A subtotal in a list or database

**Arguments**

**Function_num**: The number that specifies the function to use for the subtotal.**Ref1**: The first range, or reference for which you want the subtotal.**Ref2**,

**Remarks**

**Ref2**,... is Optional. You can specify up to 255.- Filtered-out cells always excluded.
- Function numbers 1 - 11 includes the manually-hidden rows.
- Function numbers 101 - 111 excludes the manually-hidden rows.

### List of function numbers used in the SUBTOTAL function

The following is a list of function numbers that you can use in the **SUBTOTAL**() function:

Function_num | Function_num | Function |

1 | 101 | AVERAGE |

2 | 102 | COUNT |

3 | 103 | COUNTA |

4 | 104 | MAX |

5 | 105 | MAX |

6 | 106 | PRODUCT |

7 | 107 | STDEV |

8 | 108 | STDEVP |

9 | 109 | SUM |

10 | 110 | VAR |

11 | 111 | VARP |

### SUBTOTAL Function Examples

Demo data:

A | B | C | D | E | F | |

1 | Item Code | Item Name | Qty | Unit Price ($) | Amount ($) | Date |

2 | Item2001 | HP Desktop Black | 15 | 1300 | 19500 | 6/10/2019 |

3 | Item2002 | HP Desktop Blue | 24 | 1035 | 24840 | 6/12/2019 |

4 | Item2003 | Dell Desktop Black | 25 | 1210 | 30250 | 6/13/2019 |

5 | Item2004 | Dell Desktop Blue | 22 | 1250 | 27500 | 6/13/2019 |

6 | Item2005 | Dell Desktop Green | 33 | 1150 | 37950 | 6/14/2019 |

7 | Item2006 | Sony Laptop Green | 47 | 1250 | 58750 | 6/14/2019 |

8 | Item2007 | Sony Laptop Black | 48 | 1270 | 60960 | 6/15/2019 |

**SUM and SUBTOTAL**

In this example, you'll learn the difference between **SUBTOTAL**() and **SUM**().

The following example sums all amounts in column E by using the **SUBTOTAL**() function:

The formula to sum all amounts in column E using the **SUBTOTAL **function is as follows:

=SUBTOTAL(9,E2:E8)

Here, we've used function number 9, which is the SUM function number.

The above **SUBTOTAL**() formula returns 259750.

So what is the difference if you use the SUM function?

If you use **SUM**(), the formula is as follows:

=SUM(E2:E8)

The above SUM formula returns 259750.

Both formulas return the sample result.

Now, let's see the difference between these two formulas.

Let's filter out and exclude the item named *Item2001* from the above list.

A | B | C | D | E | F | |

1 | Item Code | Item Name | Qty | Unit Price ($) | Amount ($) | Date |

3 | Item2002 | HP Desktop Blue | 24 | 1035 | 24840 | 6/12/2019 |

4 | Item2003 | Dell Desktop Black | 25 | 1210 | 30250 | 6/13/2019 |

5 | Item2004 | Dell Desktop Blue | 22 | 1250 | 27500 | 6/13/2019 |

6 | Item2005 | Dell Desktop Green | 33 | 1150 | 37950 | 6/14/2019 |

7 | Item2006 | Sony Laptop Green | 47 | 1250 | 58750 | 6/14/2019 |

8 | Item2007 | Sony Laptop Black | 48 | 1270 | 60960 | 6/15/2019 |

Now, **SUBTOTAL**() returns 240250, and **SUM**() returns 259750.

As you can see, the **SUM**() formula returns the same, while the **SUBTOTAL**() returns a different result.

Note that theSUBTOTALfunction always excludes the filtered-out cells.

#### Difference between function numbers 9 and 109

In this example, you'll learn the difference between function numbers 9 and 100 in the **SUBTOTAL** function.

The following example sums all amounts in column E by using function number 9:

=SUBTOTAL(9,E2:E8)

And the following example sums all amounts in column E by using function 109:

=SUBTOTAL(109,E2:E8)

If you don't filter or manually hide rows, both formulas return 259750.

Now, let's filter and exclude the item named *Item2001* from the list.

Still, both formulas return the same results because **SUBTOTAL**() always excludes the filter-out cells.

Next, let's hide a row that contains the item named *Item2001* from the list.

The first formula returns 259750, while the second returns 240250.

Now you have learned the differences between using function numbers 9 and 109 in the **SUBTOTAL** function.

Note that theSUMfunction always includes the hidden rows and filter-out cells.

In this tutorial, you've learned how to use the **SUBTOTAL function** in **Excel**.

**SUBTOTAL** is a function to return a subtotal in a list or database.

If you need to include the manually-hidden rows in the calculation, use function numbers 1-11.

And if you need to exclude the manually-hidden rows from the calculation, use function numbers 101-111 instead.