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

Learn and Earn Solution

So how did you get on with the activity?

The formula I used to calculate bundle 1 was

=SUMPRODUCT($B$2:$B$7,–(C2:C7=”Y”))

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 then multiplies the 1 and 0s by the cost price.  added together, these values return the total cost of the bundle.