Google Sheets is one of the best spreadsheet applications available today, and it offers a variety of functions and features that can help you analyze and manage your data with ease. One such function is the SUBTOTAL function, which enables you to calculate subtotals and totals for specific data ranges within your spreadsheet. In this article, we will explore how to use the SUBTOTAL function in Google Sheets.

What is the SUBTOTAL function?

The SUBTOTAL function is a mathematical operation that allows you to calculate subtotals or totals for specific data ranges within your spreadsheet. It is a versatile function that you can use to analyze large data sets and group them based on specific criteria.

How to use the SUBTOTAL function?

Using the SUBTOTAL function is a straightforward process. To get started, follow these simple steps:

Step 1 – Open your Google Sheets document

Step 2 – Select the cell where you want to display your subtotal

Step 3 – Enter the SUBTOTAL function in the formula bar. The syntax for the function looks like this: =SUBTOTAL(function_number, range1, [range2],…)

Step 4 – Insert the function arguments. The first argument specifies the type of calculation you want to perform. There are 11 available options in Google Sheets, ranging from simple sums to more complex calculations such as averages and percentiles.

The second argument is the range or series of cells that you want to include in your calculation. You can add multiple ranges to your SUBTOTAL function by adding additional arguments separated by commas.

Step 5 – Press enter to calculate your subtotal.

For example, let’s say you have a spreadsheet with columns for product name, units sold, and sales price. To calculate the total sales for all products, you can use the SUBTOTAL function with the SUM argument. Here’s how to do it:

– Select the cell where you want to display the total sales.
– Enter the following formula in the formula bar: =subtotal(9, C2:C500)
– Replace C2:C500 with the cells that contain your sales data.

This will total up all the values in the sales column and display the total sales in the specified cell.

Using SUBTOTAL with Filter and Sort

One of the most useful features of the SUBTOTAL function is that it can be used with the filter and sort functions in Google Sheets. This allows you to calculate subtotals based on specific criteria, such as product type or date range.

To use SUBTOTAL with filter and sort, follow these steps:

– Sort your data set by the column you want to group by.
– Use the filter function to display only the data you want to include in your subtotal calculation.
– Insert the SUBTOTAL function as described earlier, selecting the appropriate function number and data range. The data range should include only the filtered data set, not the entire range.
– Press enter to calculate your subtotal.

Conclusion

Using the SUBTOTAL function in Google Sheets is a powerful way to calculate subtotals and totals for specific ranges of data within your spreadsheet. It is a versatile function that can be used with filter and sort, and can help you analyze and manage your data more efficiently. With these simple steps outlined above, you can start building more complex calculations and analysis using the SUBTOTAL function in Google Sheets.