Hello and welcome to our new video
In this video we are going to talk about
Subtotal formula
Subtotal and sum
sound similar
Sum and subtotal works same
but what is the difference
we are going to understand in this video
we use subtotal formula
with filter
you didn't understand
let's see
open excel
here i have some data
on sheet no.1
some data of 20 students
serial number
name
marks
same copy
on sheet no.2
first of all
i convert this data in table
then i apply filter
to convert in table
we know
which tab we have to go
insert tab
in this tab we get table option
so we click it
and it ask us
you want to select
table from $A1 to C21
you want to select data
and convert it in table
i said yes
here my table has headers
i checked it
by default it check
if unchecked you can check it
i clicked ok
here you can see
from smallest to largest
from largest to smallest
z to z order
or z to a order
we have seen earlier
what are the benefits of converting in table
now what i have to do
if you see this data carefully
on serial no.1
marks 9
name is repeating
on 11 serial no.
marks 1
out of 10
on serial no.16
marks 7
what i have to do
i have to apply filter
on name
i apply filter
i want Rahul name data
after that i
sum it
i show you the problem
when we apply filter
i go to name
unchecked
select Rahul
here i clicked ok
you can see serial no.
1116
serial no. is correct
but it should not be like this
serial no.
i understood that i am applying filter
but even after applying filter
serial no. should be like this
1,2,3
so what i have to do
i have to use subtotal
first of all
here
i use
formula
sometimes it shows 0
after applying filter
when serial no. is wrong
then
marks on right hand side
shows 0
in excel version
it may show 0
this is also a possibility
i do autosum
luckily it shows 17
but what you have to do
if it shows 0
then you have to
do one thing
go back
take whole table
you have to
apply subtotal
convert it
in table
insert table
now i have to
write total
and subtotal
i use subtotal formula
we can do everything with subtotal
like
if i do autosum
it may show result
in case you
don't get result
you have to write
equals to subtotal
here it shows
subtotal
double click on mouse
after that
it shows
count on 2nd number
count on 3rd number
you have to write code
like function number
i want to sum
what is sum number
code number is 9
i will write 9
after that
it shows reference
what is reference
sum of which you want to get
i want to get
sum of this
it shows
from c2 to c21
close bracket
and enter
it shows 131
you will say
it shows from sum
but it shows problem
when we
filter out
i will uncheck
and write Rahul
see
apply subtotal
and
i selected
9
i have to put 9
and i selected
from c2 to c17
and i entered
i got 17
but serial number is not correct
i have to
correct it
i will take sheet number 2
and show you
after applying subtotal
in serial number
if i apply any filter
it will show me
serial number
it will not show me
Rahul on 1st number
Rahul on 11
what i have to do
i go to serial number
and first of all
i remove it
i wrote
equals to
apply equals to
and write subtotal
after that
we have to count
not sum
this function
shows serial number
after applying filter
it shows 1,2,3,4
so i select
it
its code is 3
first of all
we have to put 3
after that
we have to ask
reference
what i have to do
i have to
give reference
cell name is b2
i will write
b2,b2
because it will apply on all
after that
i lock range
to lock range
i have to press f4
dollar sign
and enter
1
now i apply
subtotal formula
now it is applied
now i convert it
in table
go to insert
go to table
now i repeat
what i did
in sheet no.1
we saw
after selecting Rahul
serial number
is 1,11,16
but
we are trying
1,2,3
like this
again go to name
select Rahul
and press ok
now it is done
1,2,3,9,1,7
9,1,7
if we do subtotal
here
it is showing same
if you can't see
you can
apply here
subtotal
equals to
subtotal
and
what i did
double click
name of function
sum
9
comma
C2 to C17
enter
now i have to
add more name
like Deepak
press ok
now it is 25
and serial number
1,2,3,4,5,6
add one more name
from here
when i add
Jishan
it is 7,8,9
serial number is coming
but same thing
if i try here
with Rahul
on sheet no.2
press ok
you can see serial number
is not showing
1,5,11,15,16,20
and if i
select one more name
Jishan
press ok
you can see
1,3,5,11,13,15,16
serial number is not showing
so for serial
we used counter
code was 3
so where you
use filter
try to use subtotal
rather than sum
because if you use sum
it may show 0
so we always
try to use
subtotal with filter
i hope you understood
everything
if you have any doubt
then watch this video again
I will meet you in next video till then keep practicing.