Power Of Sumproduct Formula in Excel

SUMPRODUCT is an excel function used to add the product of selected range of cells.

Syntax

=SUMPRODUCT(array1, array2,….,array_n)

First thought which comes up to an existing excel user is, why don’t we get the products of the values and use a SUM formula over it. If there are any criteria involved why don’t we use SUMIF / SUMIFS.

In order to clarify that, we need to first understand the advantages of SUMPRODUCT in place of SUMIF(S):

  1. In the SUMIIF(S) functions, while working between two excel files, source file should be kept open, whereas, in SUMPRODUCT it is not required
  2. OR logic not possible while using SUMIF(S) function, whereas, in SUMPRODCT we can use OR logic (Note: OR(+); And(*))

Let us see some practical example to understand the formula better. I have given below table which speaks about the training hours completed region wise, department wise and program category wise.

Scenario 1:

If you want to know the ‘Total training hours completed’, you need to sum up the product between program hours and programs attended. It can calculated using the below formula:

=SUMPRODUCT(D2:D10,E2:E10)

Scenario 2:

Let us say we want to calculate total functional training hours completed, in this case we have criteria to check, this can be solved using the below formula:

=SUMPRODUCT((C2:C10=”Functional”)*(D2:D10)*(E2:E10))

Scenario 3:

What happens when add more criteria, let us say we want to find total technical training hours completed by sales dept, you must use the formula with AND logic approach given below:

=SUMPRODUCT((B2:B10=”Sales”)*(C2:C10=”Technical”)*(D2:D10)*(E2:E10))

Scenario 4:

In order to find Total training hours completed by A and C region, you must use the formula with OR logic approach given below:

=SUMPRODUCT(((A2:A10=”A”)+(A2:A10=”C”))*(D2:D10)*(E2:E10))  

Click here to download excel file to practice at your end

Hope this posting has helped you to understand the power of sumproduct formula.

Happy Learning!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.