What makes the SUMPRODUCT function even more powerful is its ability to nest formulas, or in simple terms, add another function within the SUMPRODUCT function.
Instead of getting the Total Sales for a region, we can extract the Maximum sales value simply by entering the MAX function before the criteria. See how below.
Hello John,
great formula. Is it possible that this does not work with the MIN() function?
Greetings Heiko
Heiko,
Doing an F9 in the formula gives us the following: SUMPRODUCT(MAX{2500;0;2568;0;2569;0;2568;0;2562})
So the minimum will return a zero, being the values for South which are not part of our SUMPRODUCT formula.
John
Nice workaround & specially the way you present it in GIF images in few seconds
worth appreciating.
Thanks Brij, it is great to see that you liked this tutorial. SUMPRODUCT function is my favourite and if you can master that then you can do anything with your data analysis, no need for Pivot Tables:)
Cheers,
John
What’s the use of the plus sign on the second position of the formula (=+SUMPRODUCT(MAX((C15:C23=”north”)*D15:D23)))? I think none.
Albert,
I have a European keyboard so the + sign is quicker to access than the = sign. Either one does the same job!
Thanks,
John
