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
- 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/
I learned a lot through this video, Paula's an amazing instructor, as always.
As for the activity, the solution is
=SUMPRODUCT(B2:B7;--(CHOOSE(A9;C2:C7;D2:D7;E2:E7)="y"))
I used the CHOOSE function to choose between the bundles, and linked it to the a9 cell, better than writing the formula three times or modifying the formula for each different bundle
Oh I just noticed I never posted the solution to this. I will have it out in the next week or so, Sorry! Interesting solution you came up with, nice work indeed.
Well Paula that was a very challenging activity I must say. I would really encourage others to try it out as working out the formula really made the penny drop on how sumproduct works in terms of trues and falses and 1 and 0.
Looking at bundle 1, the answer should 94. I finally achieved that result with the formula =SUMPRODUCT(--(C2:C7="Y"),$B$2:$B$7)
Im looking forward to the solution.
Dernan I should have known you would be the first in here to solve this, you will have to wait now till next week when I publish the solutions to find out if you are right
@theexcelclub thank you for this. I have been trying to count the keywords in a cell for a marketing dashboard I am creating in work and I was really stuck on that. You have just made my day 💖💖💖💖💖💖
You are most welcome. Did you try the learn and earn activity?
When I was in the Army, I learned Excel on my own! I had created "master trackers" for the unit, First Sergeant, and the Unit Commander! These trackers, I had created, had auto calculations built right into the excel tracker. I had created many of these ON MY OWN with NO PRIOR EXPERIENCE nor ANYONE teaching me how to do these programs into Excel. Now, it's great seeing someone on here teaching others how to use the Excel program. It's fun and, once you learn how to do these programs, they can be of MUCH value and benefit to ANY business that need "trackers".
Nice work on learning it on your own. Many people can learn a lot on their own. However, people can also struggle when it comes to knowing the capabilities of Excel and therefore they do not get past much of the basics. I bet your tracker was awesome and is probably still in use somewhere. Thank you for stopping by and the comment.