The 2 Most Important
Excel Functions AND
Lesson 11: SUMPRODUCT Function in Excel
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.
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.
SUMPRODUCT ¦ INDEX/MATCH ¦ SUBTOTAL ¦ ISERROR ¦ HLOOKUP ¦ LOOKUP ¦ VLOOKUP
AVERAGEIF ¦ AVERAGEIFS ¦ COUNTIF ¦ COUNTIFS ¦ SUMIF ¦ SUMIFS ¦ IFERROR