Goal: Provide some advanced practical examples of the Excel SUMPRODUCT function.

This video will present some practical uses of the SUMPRODUCT function for financial or business modelling. We have financial data for various business units across a number of months. The following screenshot is covered in the below video, along with the attached Excel workbook at the bottom of this page.

Even with the advent of the SUMIF and SUMIFS functions, the SUMPRODUCT function is still a viable alternative to aggregating and computing data because it is incredibly flexible.

Excel SUMPRODUCT examples

In example 1, we will calculate Spot Gold Revenue in euros based on price, exchange rate and volume. In summary, here is the formula that computes the 3.3m euro revenue amount.

For the second illustration, SUMPRODUCT will be used to return the total EBITDA for the entire data set. The two ranges of Revenue and EBITDA % will effectively compute the total EBITDA amount. Here is the formula for the calculation of the total EBITDA figure.

The third example will aggregate Net Income in month 2. If the SUMPRODUCT function is applied correctly, then the answer shall be 84. To reiterate, here is the formula version of the SUMPRODUCT used to compute Net Income for month 2

Further SUMPRODUCT examples

The fourth installation of SUMPRODUCT will derive total Net Income for the Charlie business unit in month 1 and 4. The double hyphen in front of the first array, which in this case is the business unit column, is pivotal to the successful application of the SUMPRODUCT function in this instance.

The second array references the month range of column F. Given we need to reference two values in the month range – months 1 and 4, we need to reference the month column a second time as we are doing here.

Finally, we need to reference column J or the Net Income array of data, in order to return the correct amount of 22 for Charlie in months 1 and 4. To confirm, here is the formula again.

The fifth illustration of SUMPRODUCT will seek the total revenue for the business units, Alpha, Delta and Echo for month 3. Again remember to use the double hyphen when referencing the first array, the Month range in column F.

Similar to the previous formula in example 4, we must reference the business unit range or column B array more than once. Given we want to sum the total revenue for three business units, we have to insert the business unit array three times, as we are doing here.

Finally, we reference the Revenue array in column G. The correct amount will be 241. To recap, here is the formula again.

In conclusion

Refer to the following video for a demonstration to use the Excel SUMPRODUCT function. The example workbook with these example is free to be downloaded in the below.

We will continue this demonstration in a follow-up video, where we will look into further uses of SUMPRODUCT in financial modelling