0608 Filter - SUBTOTAL() for Calculations

Advanced Excel Crash Course Section 6: Sort and Filter
5 minutes
Share the link to this page
Copied
  Completed
You need to have access to the item to view this lesson.
One-time Fee
$99.99
List Price:  $139.99
You save:  $40
€91.73
List Price:  €128.43
You save:  €36.69
£78.50
List Price:  £109.90
You save:  £31.40
CA$135.42
List Price:  CA$189.60
You save:  CA$54.17
A$152.22
List Price:  A$213.12
You save:  A$60.89
S$133.77
List Price:  S$187.28
You save:  S$53.51
HK$781.89
List Price:  HK$1,094.68
You save:  HK$312.79
CHF 88.30
List Price:  CHF 123.63
You save:  CHF 35.32
NOK kr1,058
List Price:  NOK kr1,481.24
You save:  NOK kr423.24
DKK kr684.10
List Price:  DKK kr957.77
You save:  DKK kr273.66
NZ$164.15
List Price:  NZ$229.81
You save:  NZ$65.66
د.إ367.20
List Price:  د.إ514.09
You save:  د.إ146.89
৳10,978.23
List Price:  ৳15,369.96
You save:  ৳4,391.73
₹8,290.53
List Price:  ₹11,607.08
You save:  ₹3,316.54
RM471.80
List Price:  RM660.54
You save:  RM188.74
₦156,534.34
List Price:  ₦219,154.34
You save:  ₦62,620
₨27,950.66
List Price:  ₨39,132.05
You save:  ₨11,181.38
฿3,595.26
List Price:  ฿5,033.51
You save:  ฿1,438.25
₺3,229.78
List Price:  ₺4,521.83
You save:  ₺1,292.04
B$499.40
List Price:  B$699.18
You save:  B$199.78
R1,883.20
List Price:  R2,636.56
You save:  R753.35
Лв179.42
List Price:  Лв251.19
You save:  Лв71.77
₩133,286.20
List Price:  ₩186,606.01
You save:  ₩53,319.81
₪365.03
List Price:  ₪511.06
You save:  ₪146.02
₱5,561.09
List Price:  ₱7,785.75
You save:  ₱2,224.66
¥14,911.76
List Price:  ¥20,877.07
You save:  ¥5,965.30
MX$1,670.32
List Price:  MX$2,338.52
You save:  MX$668.19
QR364.82
List Price:  QR510.77
You save:  QR145.94
P1,359.04
List Price:  P1,902.71
You save:  P543.67
KSh13,398.66
List Price:  KSh18,758.66
You save:  KSh5,360
E£4,718.65
List Price:  E£6,606.31
You save:  E£1,887.65
ብር5,656.15
List Price:  ብር7,918.84
You save:  ብር2,262.68
Kz83,512.74
List Price:  Kz116,921.18
You save:  Kz33,408.44
CLP$93,810.03
List Price:  CLP$131,337.80
You save:  CLP$37,527.76
CN¥710.61
List Price:  CN¥994.89
You save:  CN¥284.27
RD$5,917.87
List Price:  RD$8,285.25
You save:  RD$2,367.38
DA13,435.55
List Price:  DA18,810.31
You save:  DA5,374.76
FJ$226.69
List Price:  FJ$317.37
You save:  FJ$90.68
Q780.94
List Price:  Q1,093.35
You save:  Q312.40
GY$20,949.18
List Price:  GY$29,329.69
You save:  GY$8,380.51
ISK kr13,659.63
List Price:  ISK kr19,124.03
You save:  ISK kr5,464.40
DH1,002.67
List Price:  DH1,403.78
You save:  DH401.11
L1,768.33
List Price:  L2,475.73
You save:  L707.40
ден5,652.42
List Price:  ден7,913.61
You save:  ден2,261.19
MOP$805.88
List Price:  MOP$1,128.27
You save:  MOP$322.38
N$1,866.55
List Price:  N$2,613.25
You save:  N$746.69
C$3,681.10
List Price:  C$5,153.70
You save:  C$1,472.59
रु13,266.49
List Price:  रु18,573.62
You save:  रु5,307.13
S/368.83
List Price:  S/516.39
You save:  S/147.55
K382.06
List Price:  K534.91
You save:  K152.84
SAR375
List Price:  SAR525.02
You save:  SAR150.01
ZK2,508.19
List Price:  ZK3,511.57
You save:  ZK1,003.37
L456.04
List Price:  L638.48
You save:  L182.43
Kč2,310.02
List Price:  Kč3,234.13
You save:  Kč924.10
Ft36,211.46
List Price:  Ft50,697.50
You save:  Ft14,486.03
SEK kr1,038.11
List Price:  SEK kr1,453.39
You save:  SEK kr415.28
ARS$85,066.49
List Price:  ARS$119,096.49
You save:  ARS$34,030
Bs691.23
List Price:  Bs967.75
You save:  Bs276.52
COP$389,029.38
List Price:  COP$544,656.70
You save:  COP$155,627.31
₡50,979.09
List Price:  ₡71,372.77
You save:  ₡20,393.67
L2,470.17
List Price:  L3,458.33
You save:  L988.16
₲730,160.41
List Price:  ₲1,022,253.79
You save:  ₲292,093.37
$U3,841.23
List Price:  $U5,377.88
You save:  $U1,536.64
zł395.31
List Price:  zł553.46
You save:  zł158.14
Already have an account? Log In

Transcript

Hi there, I hope you would have loved our previous videos on filter, the shortcut keys that you will be allowed to use that are expected to use while working filter. Now this time we are going to talk about the calculations the formula based calculations with the filtered list. So what I'm going to do first thing first, I pick up the data, Shift Ctrl write down, I press Alt, a T to activate the filter. Or else you can also go to data and quickly click on this filter button. So that will activate the filter option. Once that is done.

I'm going to pick up the amount column and go to the number of filters. Of course if you go to number filters, you get many more options which are numeric based in the sense that between something and something, you'll have the option here less than equal to greater than does not equal to. And right now to be able to explain and illustrate the formula which is associated with calculations under filtered list. I'm going to top 10 Of course, you can make this top five, but for the time being, I'm keeping it top 10. Now once you do top 10, it is the top 10 lists based on the numeric value. Now suppose if somebody asks you that you want the total of the top 10 values, wouldn't you be tempted to use the sum formula?

If you do so, and if you happen to choose all the values within that list, it gives you the total of all the values whether they are part of the filter or not, which means it is taking the values which are hidden inside the rules, for example, 16 and 75. And that gives you a huge result. course this is incorrect. So what is the alternative? If you want to sum under a filtered list, the easiest path to do so is you press the shortcut key or equal to and I press enter. I will get the correct total.

Now all equal to the same thing can also be done. If you were to apply a some option now a lot of people go to Home And then you find the AutoSum. They click on some, and you get the exact same formula, subtotal nine, and then after the range, enter, I get the correct total. Now you must be wondering what is the subtotal formula, I will tell you the first portion of subtotal formula which says function num. Now it appears to be a single digit, let me do one thing. I'll do that.

And I'll put one just, for example, the moment I put one, I get a drop down. And this drop down tells me a variety of values. For the time being, we'll focus only on the first 11 values. Now if you happen to see the list, you will notice that the sum has been marked with nine which means if you want the summation of all the filtered list values, you must put in nine if you want, average, put one you don't have to remember the list. It's just that you must remember the logic. If you delete the one, automatically the list appears and you can pick and choose for example, Max wood before Five will be min and one is definitely average.

So this is the average value amongst the top 10 values. So if you're wondering, what are the other options when we talk about the one zero series, I'll take these parts a little later in the next lecture probably a bit too much in a single session. So I'll come to that. But for the time being, let me also highlight your attention to one phenomenon which has seen people coming across and that could lead to some erroneous results. The right subtotal formula by hand, of course, and the right nine indicating it's the sum that they are looking for, but they go ahead and choose the entire selection manually. Now notice it is starting from 16 through and where is our actual data starting from nine through 16 through till what row 244 through.

It doesn't even take the extra rows which are hidden between 244 and do 64th row, which means in future if you extend the list to lead Top 30 clients and I do that I do that keeping the subtotal formula as it is I do that top 30 Okay, as I scroll down the list as I double click on the formula notice it has not included the value, which was falling outside the 244. So, a safe option is you press the Alt equal to to activate the subtotal formula. Now, you might tell me if there are blanks What do we do the all equal to might not capture the entire series of range. Now that you know the logic even if you apply or equal to just keep in mind that the starting points should include the first row of data, which in our case is ninth row, and in this case, 260 third is the last row of the value.

So I put e 16. manually and e 263. Being the last row in which the data exists, come what may you must include the entire range to be able to get accurate result, time and again, so There you go. This was subtotal formula in its purest and basic forms.

Sign Up

Share

Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.