0:00
hi everyone and welcome back to Excel In
0:03
this video I'm going to show you how to
0:05
calculate the sum of filter cells using
0:09
this example So as you can see I have
0:12
data for export and import uh from
0:16
different countries We've got the
0:17
country the type the month and the value
0:19
in dollars So the first thing that we
0:22
need to do we will calculate the sum
0:24
total of the values So let's do that We
0:28
will do this by using the sum the sum
0:36
formula and then just take all the this
0:44
here So the sum total of all the uh
0:48
export and imports for all these
0:52
945,000 US So for us to be able to
0:56
calculate the sum of filtered sales we
0:59
have to filter our data first of all So
1:01
let's do that So click
1:05
the headers and go to your data tab
1:10
under sort and filter Click on filter So
1:12
you apply filter on filters on your data
1:16
So let's now try and see Um let's filter
1:22
So deselect all of them Ensure that you
1:25
have your subtotal and sum total
1:28
selected Let's start with the United
1:31
States first of all Click on okay As you
1:35
can see the sum total we still get to
1:38
have the sum total of all the data But
1:41
now what we need is only the value just
1:44
here So what we will do you apply the
1:55
subtotal as you can see we have number
1:58
nine So use the number nine which is
2:03
looking for some and then
2:07
uh click and drag and drop to all the
2:10
values that you need and click enter And
2:13
as you can see we have um
2:24
this So as you can see our subtotal is
2:29
$250,000 US We can actually check for
2:32
another for another country Let's say we
2:38
Germany And as you can see for the
2:40
subtotal changes but the sum of all the
2:43
values remain the same So this is how
2:46
you can actually calculate the sum of
2:49
the filtered cells by the use of the
2:52
subtotal formula However there's
2:55
something else you need to to know As
2:57
you type in your subtotal
3:02
formula subtotal formula it has two sums
3:06
one is the one number nine and the other
3:09
one is 109 So the difference between the
3:13
two is when you use the sum 109 this one
3:17
ignores data that is not visible But if
3:20
you use the one that you've just used
3:23
sum number n this one will not ignore
3:26
any hidden rules or cells So in this
3:29
particular case ensure that you just use
3:39
nine and then of course you highlight
3:42
the data that you need
3:44
and there you go So this will keep
3:47
happening even uh let's go back and
3:50
select all If we select all what we need
3:53
to have as our subtotal it's supposed to
3:57
be 945 since we are going to have
4:00
everything selected So let's check
4:04
this It's supposed we are supposed to
4:06
have the same So let's just um start
4:16
Subtitle 9 and then all the data that we
4:21
have here all the way to
4:25
80,000 And as you can see now it's
4:29
945,000 US So I I hope
4:32
you've benefited from this video and if
4:36
you like it please leave us a thumbs up
4:38
and if you have any question please feel
4:41
free to leave it down in the comment
4:43
section And as always thank you very
4:45
much for watching and see you again in
4:47
the next video Goodbye
