MVP #70: Excel's Most Multitasking Formula -- SUMPRODUCT

Sumproduct is one of the general purpose Excel formulas I learned last. I was deceived by its simple nature to assume that I had not much special use for it.

Today, I'll be showing you the amazing use of SUMPRODUCT for common issues you'll encounter at work (working with business data).

So let's say you manage a sales team and they sell two products. Every month, you get the report of the sales from each person which you combine to have a consolidated sales report. Below is a snapshot of the sample I created.

As part of your team building strategy and sales improving tactic, you do an in-depth monthly performance report of each sales person per product with recommendations of what they could have done better. And to make this report, you constantly find yourself picking the sales for a month for a product by each salesperson. Like a probe into each person's sales figures. So what if you want something that will help you automate part of that probing. Showing you total sales by a particular sales person per specified product and specified month.

A formula that you can supply the person, the product and the month, and it will pick for you the total sales figure. Something like VLOOKUP except that VLOOKUP doesn't accept multiple lookup criteria and sum. So what formula would you use?

The answer: SUMPRODUCT

Don't be put off by the length or complexity of the formula. It is easy to grasp. What I more importantly want you to pay attention to is that whenever you have a table and want to pick a value (numeric value and a summation) based on multiple criteria that VLOOKUP can't handle, then turn to SUMPRODUCT.

I want you first to expand your mind and know what it can do plus where you'll need to use it. It has a more simple use which I have skipped because you can always get that one done without using SUMPRODUCT. But this example I am sharing with you is one of the situations that you'll have no other/easier alternative.

So say you've worked yourself into a situation that you need SUMPRODUCT like I have explained (multiple criteria to consider). How do you now use SUMPRODUCT? How does it really work. Then please read one of the best and excellently illustrated tutorials on SUMPRODUCT here: https://www.sumproduct.com/thought/multiple-criteria.html 

The explanation is way better than what I would have come up with and more in-depth than I usually do my blog posts.