I started using this function recently and I'm amazed as to what it can do. Extremely powerful bcoz it can deal with arrays. The mix of and / or logic is something no other function in Excel can handle on it's own.
Absolutely, Sachin. It is my favourite function in Excel along with INDEX. The new DA's have changed things now and SUMPRODUCT is replace. But most users don't have DA's yet, so until they became standard, SUMPRODUCT lives on with its ability to handle arrays making it one of the best.
Very helpful , thank you for this tutorial, do the amount of brackets/parenthesis matter? And if so, how do we determine how much paranthesis we need? Thank you for your educational video
Thanks Steven. Yes there are the parenthesis that come with the function. And then parentheses are enclosing each calculation inside it. The colours of the brackets can help see where you are as you progress through the calculations. Sometimes brackets can be omitted. It all depends on what it is you are doing.
this is great! is it possible to use this for material consolidation e.g multiple dispatches of material against same order number; giving a total of what's left on a purchased contract?
Thank you, Natasha. The answer to this would depend on the spreadsheet as to the exact approach. But essentially it sounds like you want the contract total minus the multiple dispatches. SUMPRODUCT can help, but is probably not necessary depending on the spreadsheet set up. Maybe a simple SUM or a SUMIFS for the total dispatches would work.
@@Computergaga that's amazing! You actually replied thank you just for that! I actually managed to figure it out. Would you like me to share it with you!!? X
Only when doing approximates matches. So if you enter 0 or false for the fourth argument (range lookup) then no, the order does not matter. If you enter true, 1 or omit the range lookup argument then it is essential that your list is in ascending order by its first column.
You could probably do a wildcard search in Find and Replace to locate them all. For example, 2* for all those beginning with 2. You could also use an IF function with LEFT to isolate the first number for testing. Display yes or no and filter the results. This technique could even be embedded in the FILTER function to return all those results with a formula - ruclips.net/video/kdl3mNEyIRE/видео.html
How do you drag down the formula so Right now i have SUMPRODUCT(D3:AV3,D6:AV6).I want to drag down this formula and have automatically generate SUMPRODUCT(D3:AV3,D7:AV7) then drag down some more and have SUMPRODUCT(D3:AV3,D8:AV8) ?
I am trying to get my head around cntrl shift enter, how come you don't have to do this with sumproduct? Is that not an array formular, especially for example sumproduct ((A1:A4=E4)*1), why don't you have to press cntrl shift enter for this?
The function is an array function. So it is different to custom made array formula such as putting SUM and IF into an array formula. So we can press Enter like normal. I love that it simplifies array calculations.
I started using this function recently and I'm amazed as to what it can do. Extremely powerful bcoz it can deal with arrays. The mix of and / or logic is something no other function in Excel can handle on it's own.
Absolutely, Sachin. It is my favourite function in Excel along with INDEX. The new DA's have changed things now and SUMPRODUCT is replace. But most users don't have DA's yet, so until they became standard, SUMPRODUCT lives on with its ability to handle arrays making it one of the best.
@@Computergaga It's amazing to have mix of old and new school. Thanks ☺️👍
Great video! Thanks
MADE MY DAY.. THANKS YOU
Pleasure 👍 You're welcome.
это произношение! это прекрасно!
This is owesom thank you for valuable support to us.
no problem. Glad to help Damodar.
Thanks for this!
You're welcome, Animesh.
Thank you so much!!!
You're very welcome Joshua.
Very helpful. Thanks a lot
You're welcome. Thank you.
Thank you!
My pleasure Asaad.
GREAT !!!
Thanks Arnaud.
Excellent video
Thank you Maximillian.
Very helpful , thank you for this tutorial, do the amount of brackets/parenthesis matter? And if so, how do we determine how much paranthesis we need? Thank you for your educational video
Thanks Steven. Yes there are the parenthesis that come with the function. And then parentheses are enclosing each calculation inside it. The colours of the brackets can help see where you are as you progress through the calculations.
Sometimes brackets can be omitted. It all depends on what it is you are doing.
easy exelent
Thanks a lot!
nice 👍🏻👍🏻👍🏻👍🏻
Thank you 👍
Nice one again mate, is sumproduct can be a alternative to sumif.Can you please make video on this alternative
Oh yes it can absolutely be an alternative to SUMIF
this is great! is it possible to use this for material consolidation e.g multiple dispatches of material against same order number; giving a total of what's left on a purchased contract?
Thank you, Natasha. The answer to this would depend on the spreadsheet as to the exact approach. But essentially it sounds like you want the contract total minus the multiple dispatches.
SUMPRODUCT can help, but is probably not necessary depending on the spreadsheet set up. Maybe a simple SUM or a SUMIFS for the total dispatches would work.
@@Computergaga that's amazing! You actually replied thank you just for that!
I actually managed to figure it out. Would you like me to share it with you!!? X
Great to hear, Natasha. Good work!
Is there any way I can add up a certain product in a list of many different items?
Yes
Hi, thnx for the video. But shouldn't the second array refer to quantitiy (20 apples) and the third one to price ( 0.20 pounds)???
Yes the column header are the wrong way around. Good spot. Does not affect the formula though.
can you please let me now the shortcut how you changed entries to absolute. ex: E2:J6 change to $E$2:$J$6
That was the F4 function key on the keyboard. Select or click amongst the range in the formula and press F4.
a question on vlookup dies the data have to be in ascending order for it to work?
does*
Only when doing approximates matches.
So if you enter 0 or false for the fourth argument (range lookup) then no, the order does not matter.
If you enter true, 1 or omit the range lookup argument then it is essential that your list is in ascending order by its first column.
how do we find a range of numbers in an array that begin with the same number?
You could probably do a wildcard search in Find and Replace to locate them all. For example, 2* for all those beginning with 2.
You could also use an IF function with LEFT to isolate the first number for testing. Display yes or no and filter the results.
This technique could even be embedded in the FILTER function to return all those results with a formula - ruclips.net/video/kdl3mNEyIRE/видео.html
@@Computergaga thanks
How do you drag down the formula so Right now i have SUMPRODUCT(D3:AV3,D6:AV6).I want to drag down this formula and have automatically generate SUMPRODUCT(D3:AV3,D7:AV7) then drag down some more and have SUMPRODUCT(D3:AV3,D8:AV8) ?
Sorry I don't know what you mean. Those formulas are used in the video.
I rewrote my post. Is it now clear?
I am trying to get my head around cntrl shift enter, how come you don't have to do this with sumproduct? Is that not an array formular, especially for example sumproduct ((A1:A4=E4)*1), why don't you have to press cntrl shift enter for this?
The function is an array function. So it is different to custom made array formula such as putting SUM and IF into an array formula. So we can press Enter like normal. I love that it simplifies array calculations.
Computergaga is your day job a data analyst role? I've been a junior data analyst but I'm trying to sharpen my skills
I work as a trainer Jayjay. Spend my days training people in Excel and other software packages.
is there a way to combine VLOOKUP and SUMPRODUCT?
Probably, not something I have had a need for.
Combine you can almost everything but indeed, what is the reason for it? The problem, you wanted to solve?
you forget about mexico
Isn't Mexico south America?
I believe it is North America. But either way SUMPRODUCT could use it as a condition.
No dummy
But you got it wrong
She had sales in Mexico as well
Nick Austin
I don’t think it is wrong, the formula was written to sumproduct only two countries, Mexico was not added to the first part of it.