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
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) ?
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 - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-kdl3mNEyIRE.html
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.
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.