0:00
hello and welcome to tutorials
0:03
point in this video we will learn how to
0:06
use the sum and sui function in Excel
0:10
also you can find the link to download
0:12
this Excel practice sheet in the
0:14
description below so let's get
0:17
started so here we have a sample data
0:21
set of items now these are fruits then
0:23
we have their quantities price per unit
0:26
and total price of these fruits now our
0:30
first task is to find the total quantity
0:33
of all these fruits together so we will
0:35
use the sum function here so here in
0:38
this cell I'll write equals sum and then
0:42
open parenthesis and then we will select
0:45
these numbers the quantity rows as you
0:48
can see all of these and then I will
0:50
close the parenthesis and hit enter and
0:56
2550 fruits next is we want want to find
1:00
the total price of all these items so we
1:04
will use simply the sum formula again
1:07
I'll write equals sum open parentheses
1:12
and then we are going to sum all of the
1:14
prices here so I will select this column
1:17
or or these total price rows and then I
1:20
will close the parenthesis and hit enter
1:24
and this is the total price of all these
1:31
dollar now we have the sum IF function
1:35
now the sum IF function is used to sum
1:39
particular values when we meet a
1:41
specific criteria so here for our
1:44
example I have again taken the same data
1:46
set we have the item quantity price per
1:49
unit and total price and I have these
1:51
questions and we are going to solve
1:53
these questions using the sum IF
1:55
function so the first question is total
1:58
price of items price at $5 per unit so
2:02
we want to find the total price of those
2:04
items which are priced at $5 per unit so
2:10
equals sum if and then parenthesis now
2:14
first thing is the range now this range
2:18
is going to be what we want to calculate
2:20
what is the criteria so here as you can
2:23
see we want to find the total price of
2:25
items priced at $5 per unit so this per
2:30
is going to be our range so I will
2:32
select this column of price per unit all
2:36
right then here I'll write comma next is
2:39
the criteria now the criteria is the $5
2:43
per unit so the criteria is here $5 so
2:47
I'll write five and then comma and then
2:50
we have the sum range we want to find
2:53
the total price of those items so the
2:56
sum range is going to be this total
2:58
price column so so I select this one
3:01
I'll close the parenthesis and hit enter
3:04
and as you can see the total price is
3:09
$2500 similarly let's move to next
3:12
question it says we want to find the
3:14
total price of items with quantity
3:17
greater than 400 here again I'll use the
3:20
sumf formula I'll write
3:23
equals some if and then parentheses and
3:26
then the range now here the range is
3:29
going to be the quantity column so I
3:32
will select the quantity column this one
3:35
and then I'll write comma then we have
3:37
the criteria now criteria is going to be
3:40
quantity greater than 400 so here I'll
3:44
write greater than 400 something like
3:48
this and then comma then we have the sum
3:51
range we want to find again the total
3:54
price of those items so I will select
3:56
the total price columns something like
3:59
this and I will just close the
4:01
parenthesis and hit enter and this is
4:05
the total price of items with quantity
4:07
greater than 400 all right next is the
4:11
total price of items starting with
4:14
letter B so here you can see the bananas
4:18
start with letter b and so do
4:20
blueberries so for this one again we are
4:23
going to use the sum F function so I'll
4:26
write equals sum F parentheses es now we
4:30
have the range now in this case the
4:32
range is going to be name of the item
4:34
because we want to look for the letter B
4:37
so I will select the names column right
4:40
here and then I'll write comma and then
4:43
the criteria is going to be the word
4:46
that starts with letter B so here I'm
4:50
going to write this B and Then star now
4:55
this b means anything that starts with b
4:59
and this star means that anything can
5:01
come after that B all right then we have
5:05
the sum rage we want to find the total
5:07
price so again I will select the total
5:09
price and here I'll just close the
5:12
parenthesis and hit enter and we have
5:15
the total price of items starting with
5:21
$5,800 all right next is total quantity
5:26
of items starting with letter b
5:30
so in this case we want to find the
5:33
quantity of items starting with letter B
5:37
so here I'll write the formula equals
5:40
some if and then parentheses and here we
5:43
want to select the range now the range
5:47
is going to be the letter B right so
5:50
this is the name so we will select the
5:52
name of the items so this column right
5:55
here and then comma and then we want to
5:58
have a criteria the criteria is again
6:01
going to be the ones that start with
6:03
letter B so just like we did earlier
6:06
I'll write B and Then star and then a
6:09
comma then we have the sum range now
6:12
here we want to find the total quantity
6:14
of items so I will select the quantity
6:17
column and now I will just close the
6:20
parenthesis and hit enter so the total
6:23
quantity of items starting with letter B
6:27
1150 now the last question is total
6:30
quantity of items priced above $4 per
6:35
unit so we want to find the quantity of
6:38
items that are priced above $4 per unit
6:42
again we'll use the sum function very
6:44
simple I'll write equals Su if and then
6:48
parenthesis now here we have the range
6:51
what is going to be our range we are
6:54
looking for this $4 per unit so I'm
6:57
going to select this price per unit
7:00
column so this will be our range and
7:02
then comma next is the criteria now here
7:05
we are looking for the $4 per unit right
7:09
so basically I'm just going to write
7:11
here greater than $4 very simple and
7:15
then we have the sum range we want to
7:18
find the total quantity of items so our
7:20
sum range is going to be this quantity
7:23
column I will just close the parenthesis
7:26
and hit enter and the total quantity of
7:29
items priced above $4 per unit is
7:34
1,600 so that is it for this video I
7:37
hope you enjoyed learning with me also
7:40
the link to this practice sheet will be
7:41
in the description below now make sure
7:44
to like this video share it with your
7:46
friends and I will see you in the next
