The Excel SUMPRODUCT function is one of the more flexible, powerful and practical tools to compute numbers in business planning. The length and breadth of Excel SUMPRODUCT speaks for itself. This article and video will identify its value in business planning, to perform both aggregation and multiplication of financial variables in a financial model.

This is a brief, introductory review of Excel SUMPRODUCT, which will keep to high-level business planning examples. Hence some more detailed, complex examples of Excel SUMPRODUCT will be explored in a follow-up article and video.

Strategize Financial Modelling Inc – SUMPRODUCT in Business Planning.xls

 

Example 1 – Aggregating Variables

Excel SUMPRODUCT effortlessly aggregates variables such as sales numbers by country, by quarter, which was demonstrated in the above video. It is a perfect tool for the financial analyst, who for example, needs to total USA’s Q2 and Q3 sales numbers with the following formula:

=SUMPRODUCT(–($B$9:$B$20=B22),($C$9:$C$20=C22)+($C$9:$C$20=C23),$D$9:$D$20)

Additionally, the financial analyst can provide management the aggregate Q2 sales for the key markets of USA, Canada and India, in one succinct Excel SUMPRODUCT formula:

=SUMPRODUCT(–($C$9:$C$20=C25),($B$9:$B$20=B25)+($B$9:$B$20=B26)+($B$9:$B$20=B27),$D$9:$D$20)

 

Example 2 – Multiplication of Variables

Excel SUMPRODUCT is an alternative to multiplying cells i.e. A1 x B1 x C1. The financial analyst can compute oil revenue based on the average sales price of oil, volume of barrels of oil sold and number of vendors; thanks to this Excel SUMPRODUCT formula:

=SUMPRODUCT(C10,C11,C12)

A further example of Excel SUMPRODUCT as a great multiplication tool, is its ability to calculate spot gold revenue in Canadian dollars for example; through the inputs of average gold price in US dollars, USD to CAD forex rate and total gold production:

=SUMPRODUCT(C18,C19,C20)

 

Example 3 – Advanced Aggregation & Multiplication

This advanced aggregation example of Excel SUMPRODUCT totals January Product volumes, based on the “Yes” Include variable, Product volumes greater than 2,000 and Products codes containing “X”. Clearly this a more complex, advanced application of Excel SUMPRODUCT for business planning purposes, which includes the addition of the ISNUMBER and SEARCH functions, as well as an “X” wildcard parameter to sum Product codes containing “X”:

=SUMPRODUCT(–(ISNUMBER(SEARCH(“*X*”,$B$9:$B$16))),–($C$9:$C$16=”Yes”),($D$9:$D$16>2000)*($D$9:$D$16))

 

Finally, an advanced multiplication example of Excel SUMPRODUCT entails computing January Profit (January Volume x January Unit Price x January Profit Margin) for only “No” Products:

=SUMPRODUCT(–($C$9:$C$16=”No”),($D$9:$D$16)*($E$9:$E$16)*($F$9:$F$16))