What is sumproduct?

Sum of the products of the the given ranges. Plese see the below picture.

When we will use sumproduct formula?

In general business scenario for calculating weighted average rate (WAR). Hold on i will tell you different ways to use this.

What is the syntax for the sumproduct formula?

See the above picture.

What is that Microsoft documentation doesn’t tell about Sumproduct formula?

Sumproduct is considered as The Formula in Excel. We can say, this is a combination of Array Formula and Normal formula. With the speed of normal formulas and with the power of array formulas this formula stands outstanding in Excel.

Sumproduct is a Reporting Tool while Pivot Table is a Analysis Tool. It is better to explain Sumproduct with a case study. Let us say you want to prepare final accounts of your organisation. For preparation of final accounts we typically start with a Trail Balance.

The picture is the Trail Balance i.e. our data table. From this see how we are calculating Branch wise profit in the below image.
In this formula Branch refers to a named range B2:B16. To create names select the range from A1:E16 and then press Ctrl+Shift+F3 and then choose create names “Top row” from the pop up window.

How to read this formula?

Get the result by summing Amt column where Branch is USA and Group is Income. Here in this formula * symbol acts as AND operator. To see how it is calculating inside just click on Evaluate formula button in Excel.

If you know sumproduct formula you need not know Sumif formula in Excel. Unlike sumif here you can have N number of conditions.

What are the other different ways we can use Sumproduct?

1. In 90% cases instead of array formulas one can use Sumproduct formula

2. Sumproduct can also be used as Vlookup , Countif, Sumif, Sum, Hlookup etc.

3.There are many other ways in which we can use this Sumproduct formula as we go i will explain these ways just be tuned.

Tweet This