Goal: Offer further advanced Excel SUMPRODUCT examples.

This is a follow-up to an earlier video on Excel SUMPRODUCT examples in financial modelling, To reiterate, here is the snapshot of the data, which this article and video will cover.

We will continue to explore some further practical applications of SUMPRODUCT, which are a lot more than were explored in the earlier video.

Excel SUMPRODUCT examples – part 2

This sixth example will aim to compute the total and included EBITDA of the above data set. First, we have to reference the Revenue data range in column G. Then the EBITDA % in column I.

And then the Include ? array in column M. The total should be 27. Here is the formula used.

For the seventh example, we will apply the SUMPRODUCT to arrive at the Net Income margin (% Revenue) for Beta over month 2 and 3. First, we have to reference the business unit array in column B.

Then the Month range in column F, which we will need to reference twice in the formula, given we want to capture months 2 and 3. This formula is a little more challenging, because we need to reference the Net Income array in column J

Then divide it by the Revenue range in column G, and then by the number of months that we are referencing – which is two months in this example.

You will return an amount of 10.59% if you correctly applied the SUMPRODUCT formula. Once more, here is the formula to calculate the Net Income margin for Beta over month 2 and 3.

The eighth SUMPRODUCT example will derive the Gross Margin for Echo over Month 1 and 3.

The first range referenced is Business Units in column B. The next array needed in the formula is Month in column F. Given we want to capture both Month 1 and 3, we need to reference this range twice.

The third array in the SUMPRODUCT formula is column G – Revenue, which the fourth array (Cost of Goods Sold) is subtracted from, in order to return 60. In summary, here is the formula to compute Gross Margin for Echo in Months 1 and 3.

Final SUMPRODUCT examples

The next example will seek to find the aggregate EBITDA across Beta, Charlie and Echo in Month 1.

First we reference column F or Month. Then the Business Unit field in column B. As we want to aggregate the EBITDA of three Business Units, we must reference the column B array three times.

Next, we reference the Revenue data in column G, which we multiple by the array in column I – the EBITDA % data set. If the formula is correctly applied, then you will return a value of 28.9

The final SUMPRODUCT example will return the total FTEs or full-time employees across the business units of Alpha, Beta and Delta for month 4.

First reference the Month array or column F. Next, you need to reference the Business Unit data, which we will need to reference three times, because we want to capture the FTEs across three Business Units.

Now we reference the Revenue array in column G, which we will be divided by the Revenue per Full-Time range in column K. You should return a value of 431.71 for total FTEs across the three Business Units in month 4.

In summary

Please refer to the following video, which walks you through how to use these advanced SUMPRODUCT examples. Further, the example Excel workbook is free download for your reference below.