The Secret Power of SUMPRODUCT in Excel

SUMPRODUCT in Excel works by multiplying value pairs together and then adding the results together.  However, SUMPRODUCT is a function that accepts arrays, making it a very powerful function beyond its basic use.

In this video, you will learn the secret power of SUMPRODUCT as we work through 5 different examples

  1. Basic traditional use SUMPRODUCT
  2. Combining SUMPRODUCT with LEN to get the character counts of a group of cells
  3. Using SUMPRODUCT to sum values based on a criteria
  4. SUMPRODUCT to create a ranking based on groupings
  5. Count keywords in a cell using SUMPRODUCT

At the end of the video, you will be given instructions about the STEEM Learn and Earn Activity.  You will find a table of data below the video which you can copy into Excel.

Learn and Earn Activity – SUMPRODUCT in Excel

Copy the following table of data into Excel

Product Cost Price $ Bundle 1 Bundle 2 Bundle 3
Product A 40 Y Y Y
Product B 43   Y Y
Product C 25     Y
Product D 33     Y
Product E 25 Y   Y
Product F 29 Y   Y
  Cost Price      

Using SUMPRODUCT, calculate the cost price for each bundle. In the comments section below, what formula did you use to calculate the bundle pricing?  Feel free to share screenshots on your work.

Do you have any comments or feedback on this article or the use of SUMPRODUCT?  If so leave a comment below and you could also be rewarded with STEEM tokens.

Next week I will post the solutions.

Sign up for my newsletter – Don’t worry, I won’t spam. Just useful Excel and Power BI tips and tricks to your inbox

Now there is value in Learning with The Excel Club and our Learn and Earn STEEM activities.

We are the first Excel, PowerBI and DAX blog in the world where you can Earn while you Learn.

Find out more now and start earning while you are learning Excel and Power BI

Learn and Earn Solution

So how did you get on with the activity?

The formula I used to calculate bundle 1 was


Where the logical expression (C2:C7=”Y”) will return a series of trues and falses. We can see this in column G of the below image.  By adding – before the logical expression, these true and false results are expressed as 1 and 0.

sumproduct in excel

SUMPRODUCT then multiplies the 1 and 0s by the cost price.  added together, these values return the total cost of the bundle.