The SUMPRODUCT function is one of the more powerful and flexible aggregation functions in Excel. This article demonstrates how SUMPRODUCT can discriminantly sum cells in either column H (“Invoice Amount”) or column I (“Amount Confirmed”), based on whether there is a corresponding amount in column I.

The following Excel spreadsheet is used in the following video to showcase SUMPRODUCT:

Sum across optional column ranges example

 

Practical financial modelling benefits of SUMPRODUCT

SUMPRODUCT is a great tool for business planning spreadsheets. If a secondary value (column I) is entered, it will override the primary value (column H). For example, a company may decide to pay a different amount (column I) compared to the invoice amount (column H).
 

How SUMPRODUCT works

There are three key elements to this complex SUMPRODUCT formula.

  ❶Array 1 This will only reference cells that contain an integer or value in column B. It will exclude any transaction, which does not contain an invoice number, because it is assumed the invoice does not exist:

=SUMPRODUCT(–($B$5:$B$51<>””),

❷Array 2 (part 1) This part of the second array will sum cells in column I that are not blank. If a transaction does contain an amount in column I, then the SUMPRODUCT will sum the amount in column I – instead of the amount in column H:

($I$5:$I$51<>””)*($I$5:$I$51)

❸Array 2 (part 2) The other part of the second array will sum cells in column H, based on when the corresponding cell in column I is blank:

($I$5:$I$51=””)*($H$5:$H$51))

 

Important thing to remember with SUMPRODUCT

As the video highlights, it is imperative the formula arrays in the SUMPRODUCT function correspond to each other;

=SUMPRODUCT(–($B$5:$B$51<>””),($I$5:$I$51<>””)*($I$5:$I$51)+($I$5:$I$51=””)*($H$5:$H$51)) and not

=SUMPRODUCT(–($B$5:$B$51<>””),($I$6:$I$52<>””)*($I$6:$I$52)+($I$6:$I$52=””)*($H$5:$H$51)).   Otherwise the above forumula will compute the wrong amount.