# which function cannot be performed through subtotal in a spreadsheet?

### Mohammed

Guys, does anyone know the answer?

get which function cannot be performed through subtotal in a spreadsheet? from screen.

## Which function cannot be performed through Subtotal in a Spreadsheet ?

Which function cannot be performed through Subtotal in a Spreadsheet ? a. Sum b. Product c. Average d. Percentage

## Which function cannot be performed through Subtotal in a Spreadsheet ?

← Prev Question Next Question →

## 1 Answer

← Prev Question Next Question →

## Find MCQs & Mock Test

Free JEE Main Mock Test

Free NEET Mock Test

Class 12 Chapterwise MCQ Test

Class 11 Chapterwise Practice Test

Class 10 Chapterwise MCQ Test

Class 9 Chapterwise MCQ Test

Class 8 Chapterwise MCQ Test

Class 7 Chapterwise MCQ Test

## Related questions

## SUBTOTAL function

How to use the SUBTOTAL function in Excel to return a subtotal in a list or database.

More functions

## SUBTOTAL function

Excel for Microsoft 365 Excel for Microsoft 365 for Mac More...

You’re invited to try Microsoft 365 for free

Unlock now

This article describes the formula syntax and usage of the **SUBTOTAL** function in Microsoft Excel.

## Description

Returns a subtotal in a list or database. It is generally easier to create a list with subtotals by using the **Subtotal** command in the **Outline** group on the **Data** tab in the Excel desktop application. Once the subtotal list is created, you can modify it by editing the SUBTOTAL function.

## Syntax

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

The SUBTOTAL function syntax has the following arguments:

**Function_num**Required. The number 1-11 or 101-111 that specifies the function to use for the subtotal. 1-11 includes manually-hidden rows, while 101-111 excludes them; filtered-out cells are always excluded.

**Function_num**

**(includes hidden rows)**

**Function_num**

**(ignores hidden rows)**

**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. The first named range or reference for which you want the subtotal.

**Ref2,...**Optional. Named ranges or references 2 to 254 for which you want the subtotal.

## Remarks

If there are other subtotals within ref1, ref2,… (or nested subtotals), these nested subtotals are ignored to avoid double counting.

For the function_num constants from 1 to 11, the SUBTOTAL function includes the values of rows hidden by the **Hide Rows** command under the **Hide & Unhide** submenu of the **Format** command in the **Cells** group on the **Home** tab in the Excel desktop application. Use these constants when you want to subtotal hidden and nonhidden numbers in a list. For the function_Num constants from 101 to 111, the SUBTOTAL function ignores values of rows hidden by the **Hide Rows** command. Use these constants when you want to subtotal only nonhidden numbers in a list.

The SUBTOTAL function ignores any rows that are not included in the result of a filter, no matter which function_num value you use.

The SUBTOTAL function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. For example, when you subtotal a horizontal range using a function_num of 101 or greater, such as SUBTOTAL(109,B2:G2), hiding a column does not affect the subtotal. But, hiding a row in a subtotal of a vertical range does affect the subtotal.

If any of the references are 3-D references, SUBTOTAL returns the #VALUE! error value.

## Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

**Data**

120 10 150 23

**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**

The SUBTOTAL function always requires a numeric argument (1 through 11, 101 through 111) as its first argument. This numeric argument is applied to the subtotal of the values (cell ranges, named ranges) that are specified as the arguments that follow.

### Need more help?

Expand your skills EXPLORE TRAINING

Get new features first

JOIN MICROSOFT OFFICE INSIDERS

## SUBTOTAL Function in Excel

The SUBTOTAL function in Excel allows users to create groups and then perform various other Excel functions such as SUM, COUNT, AVERAGE, PRODUCT, MAX, etc.

## SUBTOTAL Function in Excel

Returns an aggregate result for data provided

Written by CFI Team

Updated November 27, 2022

## 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.

### Formula

**SUBTOTAL = (method, range1, [range2 …range_n])**

Where method is the type of subtotal you wish to obtain

Range1,range2…range_n is the range of cells you wish to subtotal

### Why do we need to use SUBTOTALS?

Sometimes, we need data based on different categories. SUBTOTALS help us to get the totals of several columns of data broken down into various categories.

For example, let’s consider garment products of different sizes manufactured. The SUBTOTAL function will help you to get a count of different sizes in your warehouse.

To learn more, launch our free Excel crash course now!

### How to use the SUBTOTAL Function in Excel?

There are two steps to follow when we wish to use the SUBTOTAL function. These are:

Formatting and sorting of the provided Excel data.

Applying SUBTOTAL to the table.

Let’s understand this Excel function with the help of an example. We use data provided by a garment manufacturer. He manufactures T-shirts of five different colors, i.e., White, Black, Pink, Green, and Beige. He produces these T-shirts in seven different sizes, i.e. 2, 4, 6, 8, 10, 12, 14. The relevant data are below:

The warehouse manager provides random data. Now for the analysis, we need to get the total number of T-shirts of each color lying in the warehouse.

### Step 1

First, we need to sort the worksheet on the basis of data we need to subtotal. As we need to get the subtotals of T-shirts by colors, we will sort it accordingly.

To do that, we can use the SORT function under the Data tab.

### Step 2

The next step would be to apply the SUBTOTAL function. This can be done as shown below:

Select the Data tab, and click on SUBTOTAL.

When we click on it, the Subtotal dialogue box will appear as shown below:

Now, click the drop-down arrow for the “At each change in: field.” We can now select the column we wish to subtotal. In our example, we’ll select Color.

Next, we need to click the drop-down arrow for the “Use function: field.” This will help us select the function we wish to use. There are 11 available functions. We need to choose depending on our requirements. In our example, we’ll select SUM to find out the total number of T-shirts lying in each warehouse.

Next, we move to “Add subtotal to: field.” Here we need to select the column where we require the calculated subtotal to appear. In our example, we’ll select Number of Units in Warehouse I and Warehouse II.

After that, we need to click OK and we will get the following results:

As we can see in the screenshot above, the subtotals are inserted as new rows below each Group. When we create subtotals, our worksheet is divided into different levels. Depending on the information you wish to display in the worksheet, you can switch between these levels.

The level buttons in our example are images of buttons for Levels 1, 2, 3, which can be seen on the left side of the worksheet. Now suppose I just want to see the total T-shirts lying in the warehouse of different colors, we can click on Level 2.

If we click on the highest level (Level 3), we will get all the details.

To learn more, launch our free Excel crash course now!

### Tips for the SUBTOTAL function:

### Tip #1

Let’s assume, we wish to ensure that all colors of T-shirts are available in all sizes in either of the warehouses. We can follow these steps:

Step 1: Click on Subtotal. Remember we are adding one more criterion to our current Subtotal data.

Now,

Step 2: Select COUNT from the drop-down menu, and Size from the “Add subtotal field to.” After that, uncheck the “Replace current subtotals.” Once you click OK, you will get the following data:

Guys, does anyone know the answer?