Excel SUMPRODUCT Function - A Guide to a Powerful Excel Function

A definitive guide to the SUMPRODUCT function in Excel. This function is a hidden gem that always surprises users when they see it.

This video tutorial walks through 3 different examples of the SUMPRODUCT function. It begins with the classic use of the formula. It then demonstrates 2 more complex examples.

The Excel SUMPRODUCT function sums the product of 1 or more arrays. Excel users have taken it far beyond its initial design and this awesome function can now be found solving many daunting Excel tasks.

Here are the timings of the video.
00:00 - How to use SUMPRODUCT function in Excel
02:00 - Multiple criteria count with SUMPRODUCT
06:33 - Multiple criteria sum with SUMPRODUCT function

Find more great free tutorials at;

*** Online Excel Courses ***

Connect with us!

[damodarmishra]

This is owesom thank you for valuable support to us.

[ismokepot]

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

[TruthofDilly]

Is there any way I can add up a certain product in a list of many different items?

[vidy]

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)???

[jayjayf]

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?

[pravimanju]

can you please let me now the shortcut how you changed entries to absolute. ex: E2:J6 change to $E$2:$J$6

[sachinrv]

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.

[kirandeepchoudhry]

Nice one again mate, is sumproduct can be a alternative to sumif.Can you please make video on this alternative

[encapsulatio]

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) ?

[natashaburgess]

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?

[jayjayf]

a question on vlookup dies the data have to be in ascending order for it to work?

[maximillianramirez]

how do we find a range of numbers in an array that begin with the same number?

[divyangchoudhary]

is there a way to combine VLOOKUP and SUMPRODUCT?

[nickaustin]

But you got it wrong

She had sales in Mexico as well