What is Excel SUMPRODUCT formula and how to use it?

Today we will learn a new and exciting excel formula – the all powerful SUMPRODUCT.
At the outset SUMPRODUCT formula may not seem like all that useful. But once you understand how excel works with  lists (or arrays) of data, the SUMPRODUCT’s relevance becomes crystal clear.

SUMPRODUCT formula – syntax and usage

The sum-product formula syntax is very simple. It takes 1 or more arrays of numbers and gets the sum of products of corresponding numbers.
The syntax is =SUMPRODUCT (list 1, list 2 ...)
So, for ex: if you have data like {2,3,4} in one list and {5,10,20} in another list, and if you apply SUMPRODUCT, you will get 120 (because 2*5 + 3*10 + 4*20 is 120).
At this point it might seem like an almost useless function. But all that will change in the next 2 minutes, keep reading.

SUMPRODUCT and Arrays

Lets say you have a list of sales data with columns Name, Region, Product and Sales. Now, you want to know how many units the sales person named “Luke” sold. This is simple, you will write a SUMIF formula  and use the Name column as “criteria range” and Sales column as “sum range”.
But, wait a second, you want to find how many units sales person “Luke” sold in the region “west”.
Hmm…. we have 2 options,

• Use an array formula
• Use a pivot table 

================================

Excel Pivot Tables  : What is a Pivot Table and How to Make one

Excel pivot tables are very useful and powerful feature of MS Excel. They can be used to summarize, analyze, explore and present your data.
In plain English, it means, you can take the sales data with columns like salesman, region and product-wise revenues and use pivot tables to quickly find out how products are performing in each region.
In this tutorial, we will learn what is a pivot table and how to make a pivot table using excel.

Click here to see a video tutorial of making pivot tables in excel

Example uses of Pivot Tables

As I said before pivot tables are very powerful and useful. There are numerous uses of pivot tables that we can talk about them until Christmas.
Here are some example uses of pivot tables:

• Summarizing data like finding the average sales for each region for each product from a product sales data table.
• Listing unique values in any column of a table
• Creating a pivot report with sub-totals and custom formats
• Making a dynamic pivot chart
• Filtering, sorting, drilling-down data in the reports without writing one formula or macro.
• Transposing data – i.e. moving rows to columns or columns to rows. [learn more]
• Linking data sources outside excel and be able to make pivot reports out of such data.

Excel Pivot Table Tutorial: How to create your first pivot table

Let us make your first pivot table. We will use example data in the following format. with the data.

Step 1:  Select the data
Select the data range from which you want to make the pivot table.
Step 2:  Go to Insert ribbon and click on new Pivot table option
To insert a new pivot table in to your spreadsheet, go to Insert ribbon and click pivot table icon and select pivot table option.
Step 3:  Select the target cell where you want to place the pivot table. For starters, select New worksheet.
Excel will display a pivot table wizard where you can specify the pivot table target location etc. Select “New worksheet” option and your pivot table will be placed in newly created worksheet.
Step 4:  Make your first pivot report
The pivot report UI is very intuitive and sandbox like. To make powerful analysis, all you have to do is drag and drop fields in to the pivot table grid area. In excel 2007, you can also control this by using the “Pivot table panel”.
The pivot report is divided in to header and body sections. You can drag and drop the fields you want in each area. The body itself contains three parts. Rows, Columns and Cells. You can use any fields in these areas too.
For the above sample data, I have set this criteria:

And the outcome is this pivot report.

It might be a bit difficult to understand how this works. But believe me, if you have seen any reports or worked with any other reporting systems, then the idea of pivot tables, pivot reports and pivot charts becomes quite simple to you.
You can use the excel pivot table features to make a more complicated pivot report like this in no time.

Some useful tips on Excel Pivot Tables

• You can apply any formatting to the pivot tables. MS Excel has some very good pivot table formats (and they are better in Excel 2007 and 2010).
• You can easily change the pivot table summary formulas. Right click on pivot table and select “summarize data by” option.
• You can also apply conditional formatting on pivot tables although you may want to be a bit careful as pivot tables scale in size depending on the data.
• Whenever the original data from which pivot tables are constructed, just right click on the pivot table and select “Refresh Data” option.
• If you want to drill down on a particular summary value, just double click on it. Excel will create a new sheet with the data corresponding to that pivot report value. (This is extremely useful)
• Making a pivot chart from a pivot table is very simple. Just click on the pivot chart icon from tool bar or Options ribbon area and follow the wizard.

 ===================================================================

Actually, there is a hidden third option, use SUMPRODUCT.
That is right, my friend, we can use SUMPRODUCT to do just this (and much more).

Using SUMPRODUCT as an array formula

Assuming, the data is in range A1:D10, with Name in column A, Region in B, Product in C and Sales in D, the SUMPRODUCT formula is,
=SUMPRODUCT(--(A1:A10="Luke Skywalker"),--(B1:B10="West"),D1:D10)
Okay, lets take a minute and try to understand WTF (what the formula) is doing.

• The portion --(A1:A10="Luke Skywalker")is looking for Luke Skywalker across planetary systems in all universes It is going to give us a bunch of ONEs and ZEROs, one if the cell has Luke, Zero if the cell has something else.
• The portion --(B1:B10="West")is doing the same, but gets 1s when the value is “West”.
• The portion D1:D10 is just returning all the sales figures.
• When you put everything together and multiply, it just works. Why? That is your home work to figure out.

Share your SUMPRODUCT formula Tips & Tricks

SUMPRODUCT formula can do much more once you understand how it works. This post is meant to open the door for you. Go ahead and explore the possibilities, then come back and share your tips with us.