The 2 Most Important

Excel Functions AND
Formulas

SUMPRODUCT

AND

INDEX/MATCH

Lesson 11: SUMPRODUCT Function in Excel

A new formula:

Since 2007, Excel includes the SUMIFS function.
The SUMIFS function is similar to the SUMIF WorksheetFunction
but it enables you to check for more than one criteria.
The page Page SUMIFS will show you how to use the Excel
SUMIF and SUMIFS Functions in VBA.

SUMPRODUCT is the most important, useful and powerful function in Excel.

Example of Data, Report and Chart:

You get data from central databases, accounting, sales and manufacturing programs in the format below and you need to develop automated reports.

In your set of data there can be 500 lines, 65,000 lines and even 1,000,000 lines (Excel 2007). The order of the data may differ from time to time data can be sorted by date, by product, by city, etc.. There can be more columns (on salesperson,, time, department, store, etc.) yet SUMPRODUCT will handle it all.

** The totals include all the data in the database not just the data in the picture above.

You need a report and chart (Sales by City ($) as above) that feeds on the set of data. This report must be refreshed AUTOMATICALLY when you add data to the dataset, when you modify the data or when you copy/paste another set of data.

There could also be other reports (Sales by Month ($), Sales by City (Quantity), Sales by Month and City ($), etc.) that feed on the same set of data and they need to be refreshed AUTOMATICALLY altogether.

To achieve such a task you will develop a single SUMPRODUCT formula in the top/left cell of each table (Bikes/Montreal in the example above) and be able to copy/paste it all over the table. This formula shouldn't need to be modified whatever the dataset.

In the 2007 version of Excel Microsoft has added 4 new functions SUMIFS,  COUNTIFS, AVERAGEIF and AVERAGEIFS. But in earlier versions of Excel there has always been the SUMPRODUCT function that could do everything that these 4 functions can do and even more.

Tutorial and Examples

With SUMPRODUCT you can sum, calculate averages, use one or ten conditions, use any number of values per condition. You can use SUMPRODUCT with other functions and use SUMPRODUCT to do things that VLOOKUP cannot do.