What is the formula for subtotal in Excel?

Example

Data
Formula	Description	Result
=SUBTOTAL(9,A2:A5)	The sum of the subtotal of the cells A2:A5, using 9 as the first argument.	303
=SUBTOTAL(1,A2:A5)	The average of the subtotal of the cells A2:A5, using 1 as the first argument.	75.75
Notes

How do you sum subtotals in Excel?

Using the Subtotal Function to Sum Filtered Data in Excel

1. Display workbook in Excel containing data to be filtered.
2. Click anywhere in the data set.
3. Apply filter on data.
4. Click below the data to sum.
5. Enter the Subtotal formula to sum the filtered data.

What does Subtotal 9 do in Excel?

SUBTOTAL(9,A1:A10) finds the sum of values in cells A1:A10 where as SUBTOTAL(109,A1:A10) will find sum of values in visible rows only.

Why use Subtotal instead of sum in Excel?

The big difference between SUBTOTAL and SUM is that SUBTOTAL can be used repeatedly in the same column for section subtotals and then used again at the end for a grand total. SUBTOTAL(9, myrange) excludes other SUBTOTAL-calculated values within myrange.

What is Subtotal 109 Excel?

The SUBTOTAL function ignores rows hidden by a filter and calculates the correct result. Note: 109 is the argument for Sum if you use the SUBTOTAL function.

What does Subtotal 3 do in Excel?

What is the SUBTOTAL Function in Excel? The SUBTOTAL Function[1]in Excel allows users to create groups and then perform various other Excel functions such as SUM, COUNT, AVERAGE, PRODUCT, MAX, etc. Thus, the SUBTOTAL function in Excel helps in analyzing the data provided.

Why is my subtotal formula not working?

Excel Subtotal not working – common errors If your Subtotal formula returns an error, it’s likely to be because of one of the following reasons: #VALUE! – the function_num argument is other than an integer between 1 – 11 or 101 – 111; or any of the ref arguments contains a 3-D reference.

What does?# Mean in Excel?

Excel spreadsheets display a series of number or pound signs like ##### in a cell when the column isn’t big enough to display the information. It also happens if you have a cell formatted to display something different than what you need the spreadsheet to show.

What is the difference between subtotal and total?

Total is used to describe the final, overall sum of the other sets of numbers or subtotals. in contrast, subtotal describes the total of one set of numbers that will later be added to another set. Subtotals are used to show a calculation that forms part of a larger total sum.

How do you subtotal groups in Excel?

Highlight the whole set of data, and from the Data tab, select ‘Group’ from the Outline area. Select Rows or Columns, or both, to set up the grouping. Click OK. To summarize the data, with a range, or the whole set of data selected, click on ‘Subtotal’ in the Outline section of the Data tab.

How do you use the subtotal function?

1. On the Data tab, in the Outline group, click Subtotal. The Subtotal dialog box is displayed.
2. In the At each change in box, click the nested subtotal column.
3. In the Use function box, click the summary function that you want to use to calculate the subtotals.
4. Clear the Replace current subtotals check box.

How do you subtotal a column?

Select a cell in the list, and on the Excel Ribbon, click the Data tab, then click Subtotals. In the “At each change in” box, select the first column that you want to base the subtotals on — Category in this example. Select the function that you want to use when totaling the columns.

Why sum formula is not working in Excel?

Check the Cell Format for Text If the format shows Text, change it to Number. When a cell is formatted as Text, Excel makes no attempt to interpret the contents as a formula. After you change the format, you’ll need to reconfirm the formula by clicking in the Formula Bar and then pressing the Enter key.

Why is Excel not calculating my formula?

The most common reason for an Excel formula not calculating is that you have inadvertently activated the Show Formulas mode in a worksheet. To get the formula to display the calculated result, just turn off the Show Formulas mode by doing one of the following: Pressing the Ctrl + ` shortcut, or.

How do you do Subtotal 109 in Excel?

The Excel SUBTOTAL function with function_num 101-111 neglects values in hidden rows, but not in hidden columns. For example, if you use a formula like SUBTOTAL(109, A1:E1) to sum numbers in a horizontal range, hiding a column won’t affect the subtotal.

How to create a subtotal in Excel?

At each change in – this drop-down list displays all fields in your list.

Use function – this gives you a choice of 11 functions.

Add subtotal to – this list box lists all the fields in your list.

Replace current subtotals – if this box is checked,any existing subtotal formulas are removed and replaced with the new subtotals.

How do you outline subtotals in Excel?

Sort according to the data you want to outline.

Select the Data tab,then locate the Outline group.

Click the Subtotal command to open the Subtotal dialog box.

In the At each change in field,select the column you want to use to outline your worksheet.

In the Use function field,choose from the list of functions that are available for subtotaling.

What does the subtotal function do in Excel?

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

How to sum up subtotals?

Click the Data tab.

Click Subtotal in the Outline group.

In the resulting dialog,choose Vendor from the At Each Change In dropdown,choose Sum from the Use Function dropdown,and check Invoice Amount in the Add Subtotal To list

Check the Replace Current Subtotals and Summary Below Data options if necessary.