Excel Lesson 17 of 30: Excel SUMPRODUCT

I use SUMPRODUCT daily to solve all kinds of business data problems. It is the most powerful and simple function in Excel.

Here are a few examples of what can be done with SUMPRODUCT
Note: SUMPRODUCT was not originally designed to solve the problems below so you cannot call SUMPRODUCT using the function icon or from the menu bar "Insert/Function" to solve these problems. The dialog box offered by Excel is totally useless in these situations. You need to manually develop the formulas:  "=SUMPRODUCT((....."

Case 1

Here is a typical set of sales data. But imagine a set of accounting data, manufacturing data, planning data....there can be 65,000 lines and 256 columns in the table and the order of the data in unimportant, SUMPRODUCT will do the job.

With a SUMIF formula you can find the total sales for New York =SUMIF(N1:N7,"New York",P1:P7) but with SUMPRODUCT:

- you can add sales (Amount or quantities) for bicycles sold in New York (2 criteria)
- you can add sales (Amount or quantities) for bicycles sold in New York in store S001 (3 criteria)
- you can add sales (Amount or quantities) for bicycles sold in New York in store S001 in the month of December (4 criteria)
- you can add sales (Amount or quantities) for bicycles sold in New York in store S001 in the month of October, November and December (4 criteria with multiple values for one of them)
- you can count the number of different products sold in New York in store S001 (counting with SUMPRODUCT using multiple criteria)..........

With SUMPRODUCT you can design 5 different reports looking at the same set of data (by city, by month, by product or any other combination) and when you add new data or when you replace the set of data (copy/paste) the reports are automatically refreshed.

Case 2

With SUMPRODUCT you can extract the price of a car from the list above based on the make AND the model AND the year. No other formula can do that. So on an invoice or quotation spreadsheet you enter the make, the model and the year and a SUMPRODUCT formula will find the price for you automatically. No manual entry, no mistake. You can lookup in any table with 1, 2, 3 or any number of criteria with SUMPRODUCT.

Case 3

You have a list of 3,000 names of people with the State they reside in and their annual salary. You need a simple formula that will extract a tax rate from a table like the one above. What is the rate for a New York resident who earns $59,254? What is the rate for an Oregon resident who earns $125,654? The ONLY solution is SUMPRODUCT.