Hello and welcome to our new video
In this video we are going to learn about sum product
formula
What does sum product do?
There are two things coming
One is product and other is sum
So who will do sum?
There will be two things
Product will do
Multiply Multiply two things
After that sum
Let's see how sum product works in excel
Here I have some data
What is this data?
These are some computer accessories
Like ROM, gaming console, speaker, smart phone, pen drive,
smart panel, monitor, pen tab, pen drive and adapter
These are serial numbers
Here are number of pieces
How many number of pieces I have bought
I have bought three pieces of gaming console
And how much it cost me?
One piece cost me 90,000
Now what I have to do?
What I do in normal cases?
Without sum product formula
What can I do?
I just have to find out 3000 x 2
This is what we do in normal cases
Let me show you
Let me copy the format
Here Paste special
And format
And let's make it bigger
Ok What I do in normal cases?
I just put equals to sign
And the value given to me
Market value
I multiply it
With number of pieces
And I get the answer 6000 Now I drag this 6000
Drag it or double click
On this plus button
I double clicked on plus button
So now
All the number of pieces
I got the answer
Now what I have to do?
I have to sum it
What is the grand total?
I just put equals to sign
And sum
And here
I put number 1
And number 2
So this is number 1
And from here to
Column I need sum
It is selected
I close it and enter
What is the total? 690764 This is the total
Now let's see
How my sum product formula
Works This is also important to know
I apply sum product formula here
I put equals to
And here I write sum
See my sum product is coming down
Let's double click it
It asks me to tell array 1
What is the meaning of array 1?
Array means
Homogeneous elements
Like in programming
If there are numbers
You can store the numbers
In array
But the number will be same
It will not be a decimal number
So here
What we have to do in array 1?
We have to put a range in array 1
Like the range of this column
We selected the range of this column
After that
I have to apply multiply
And what I have to do in multiply?
I have to multiply my market value
After doing this
I close it and
Enter 619764 Now I don't have to
Multiply it first
And then do the grand total
Here are two steps
First I am multiplying the number of pieces
From market value
And at the end I am adding it
So sum product
Works like this
Let's see in sheet 2
Some data is happening
This is the same data
But here I have increased the number of
Accessories Like you will get to see rom here
And here
You will get to see gaming console here
And here
Now what we have to do?
All the accessories are written here
Now see
Rom is coming twice
Gaming console is coming twice
So what I want is
My formula of sum product
Multiply 2 by 3000
Which we have seen in last sheet
But we want
If rom is coming twice
Then multiply it by 2
And sum it
Now do you remember
There used to be a sub-total formula
We used to group and ungroup
What used to happen in group?
Things used to hide
What used to happen in that?
In that
Restriction was
If your data is written differently
Like here rom is written
And here rom is written
So it didn't work on this
It used to work on
If your rom is written 4-5 times
Then it used to form a group
And then it used to sum it
But if there is a case
Where your data is written in different places
Then you can use sum product
How to use it?
Let's see
We have to put
Equals sign
And write sum
And it will suggest you sum product
You have done sum product
Now it is saying
You have to take array 1, array 2, array 3
Now what I have to do?
What I have to get out of accessories?
I have to find the value of
I have to find the value of rom I have to find the value of
rom
First I have to select
Array 1
After that
I have to put one more bracket
I will tell you the benefit of that bracket
I have put one more bracket
And in this
I have told you specifically
What I have to find
I have done J9
I have told you
We have to get the items from J9
Specifically for rom
If rom is showing twice
Then multiply its market value
And number of pieces
And tell me
And close it
Then I will multiply it
By number of pieces
I have done number of pieces
After this
I have to put
Array 3
After that
I have to enter here
I have entered here and then I closed the bracket
Now see bracket 1 which we opened here we closed it here
and here we have told that from this we have to take and
from this we have to take F9 and after this we have
multiplied right and here after that we have selected from
H9 to H28 so now what we have to do when we enter here and
what I have got 12000 3000 multiplied to 6000 and here
again what happened 6000 so 6000 and 6000 how much 12000
now if I bring it down drag it down 900 150 multiplied by 3
how much 450 in such case you may get an
I will tell you the reason for that we have to lock the
range if someone is showing the wrong value then what we
have to do
I will delete it what you have to do this J9 value this
will change every time
I know that here second time gaming console will be there,
third time speaker will be there what you have to do leave
this and all the other ranges we have to lock it and we
know how to lock it
F9 to F28
F4 after that I have locked all the parts from G9 to F4 now
this formula is ready now what I have to do here this is
wrong one second now I have to enter here so this is 12000
now if someone is showing the wrong value
I have locked it
I hope you liked this video if you have any doubt then play
this video again and I will meet you in the next video till
then keep practicing