Tips and Help on Excel and VBA for Excel

 

Excel Chapter 13 of 24: Excel SUMPRODUCT

The most important function in Excel

IMPORTANT NOTE: To achieve what is shown below with SUMPRODUCT you cannot use the "Insert Function" dialog" window:

You need to key in the formula.

Exercise

Try the short exercise below and you will be convinced that SUMPRODUCT is the most useful and powerful function in Excel. Try it and if it doesn't work do not hesitate to send me your spreadsheet I will correct it for free with pleasure ([email protected]).

My clients and I use SUMPRODUCT daily to develop automated reports looking at sets of data imported from all kinds of outside databases (Oracle, SQL Server, Oracle, SAP) and  programs (accounting, manufacturing, sales, etc.). You add data to the set of data or you replace the set of data and the report is automatically updated. The order of the data is irrelevant and you can have up to 65,000 rows of data and SUMPRODUCT finds it all.

The formula in cell E5 is as follow:

=SUMPRODUCT((A2:A10=E3)*(B2:B10=E4)*(C2:C10)) copy/paste it in cell E5

In plain English this formula says: sum range C2 to C10 if in A2 to A10 there is the value in cell E3 (bikes) and if in B2 to B10 there is the value in cell E4 (Detroit)

Change the value in cell E3 and see the result change

Change the value in cell E4 and see the result change

Add lines of data to the database using (Detroit or Chicago) and (Bikes or Skis)

Change the data in the database (the order is not important).

In this example there are 2 criteria (city and product) but there can be any number including dates. In this example we are calculating a sum but you can also count, calculate averages....You can also use SUMPRODUCT as a VLOOKUP on steroids with as many criterias as you need.....

With SUMPRODUCT you can AUTOMATE most of your reports based on sets of data that you download from all kinds of databases and large programs. With SUMPRODUCT you can develop very rapidly and very simply permanent inventories, management  applications by project, by department, by piece of equipment, by product.....time management applications and so on.

If somebody tells you that he is an expert in Excel and he doesn't start by sharing his knowledge of SUMPRODUCT....beware.

A client who had been using Excel for 20 years before discovering SUMPRODUCT has said: "I feel like a reborn data analyst since I have discovered SUMPRODUCT."

Tutorial and Application

You can design 20 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.

Learn much more on SUMPRODUCT in a special extensive tutorial that is part of 50 Excel Spreadsheets that will change the way you work with Excel. Whatever your knowledge of Excel you will benefit from these spreadsheets.

Purchase and download "excel-tutorial-sumproduct.xls" (see the advertisement below)

When you enter the in's and the out's you want your inventory to be updated AUTOMATICALLY. If the stock of a certain item is low you want Excel to tell you. No need for complex macros just use SUMPRODUCT.

Learn how to do this with detailed explanations in  "excel-template-inventory.xls" (see the advertisement below)

Build a single report template and with SUMPRODUCT just select a branch,  department, a sales rep...among 50 and the report is AUTOMATICALLY refreshed. No more spreadsheets with 50 templates to maintain.

Learn how to do this with detailed explanations in "excel-example-dynamic-report.xls" (see the advertisement below)

 

Next Chapter: INDEX/MATCH Formulas in Excel

 

50 Excel spreadsheets, 2 Ebooks and Video Clips
A single download that will teach you
Simple ways to do more with Excel
(For beginners, intermediate and advanced users)

 

    Tips and Help on Excel and VBA for Excel

SUMPRODUCT Function in Excel