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
- Basic traditional use SUMPRODUCT
- Combining SUMPRODUCT with LEN to get the character counts of a group of cells
- Using SUMPRODUCT to sum values based on a criteria
- SUMPRODUCT to create a ranking based on groupings
- 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
Posted from my blog with SteemPress : http://theexcelclub.com/the-secret-power-of-sumproduct-in-excel/