[Google Sheets] Use useful SUBTOTAL functions for subtotal summation.
SUBTOTAL function is a useful function for calculating subtotal in Google Sheet or Excel. This function exists in Excel as well as in Google Sheet.
A situation in which a SUBTOTAL() function is required
Calculates the partial sum of the columns (vertical) on the sheet. We get the same total of the two below, the first total is 1,600,000 won and the second total is 1,200,000 won. If it's normal, it's right to pay 1,200,000 won like on the right. This is because the experience fee and meal fee are subtotal, so if you add them all up, it will be duplicated.
- 16,000,000: The amount is added up to the subtotal, so it is an incorrect value.
- 1,200,000 : This is correct because subtotal is not duplicated.
It's a very easy function to use in summation expressions with subtotal.
How to use the SUBTOTAL() function
SUBTOTAL is not necessarily a function that calculates the sum of the ranges.
Various values are available as shown below, but it is recommended to remember SUM(), which is the most commonly used number 9 or 109.
Phrases
SUBTOTAL (function code number, range 1, [range 2, ...])
Function code number
| Code (including hidden rows) | Code (ignore hidden rows) | Features | Translation |
|---|---|---|---|
| 1 | 101 | AVERAGE | Average |
| 2 | 102 | COUNT | Number of cells with numbers |
| 3 | 103 | COUNTA | Number of cells with numbers or characters |
| 4 | 104 | MAX | Highest |
| 5 | 105 | MIN | Lowest |
| 6 | 106 | PRODUCT | Multiplication |
| 7 | 107 | STDEV | Sample Standard Deviation |
| 8 | 108 | STDEVP | Standard Deviation |
| 9 | 109 | SUM | Total |
| 10 | 110 | VAR | Sample Variance |
| 11 | 111 | VARP | Dispersion |
Examples of Use
Let me explain how to use only the most commonly used SUM.
- Experience fee is the subtotal cost of using bungee jumping, rail bike, Udo submarine, and 9.81 park. In fact, here you get the same result as the SUM() function or the SUBTOTAL() function.
=SUBTOTAL (9, F5:F8) - Food cost is also a subtotal cost for Hallasan bibimbap, black pork cutlet, and meat noodles. Similarly, here you get the same result as the SUM() function or the SUBTOTAL() function.
=SUBTOTAL (9, F10:F12) - The important part is to calculate the sum, which is also to use the SUBTOTAL() function, not the SUM() function. You can set the entire range. The SUBTOTAL() function automatically excludes the SUBTOTAL() function value used in the subtraction. So the final result comes out normally.
=SUBTOTAL (9, F3:F14)
Comments