Excel’s Sumif and Sumproduct functions do two things at once in a financial model. Sumif totals numbers that meet a particular criterion, although if you’ve got multiple criteria you’ll want to take a closer look at its cousin Sumifs.
Sumproduct takes two columns of numbers, multiplies the first number in the first column by the second number in the second column and so on down the set. It then takes the sum of all those individual products. For examples see the spreadsheet (Sumif and Sumproduct). Cell E15 shows Sumproduct at work. Cell G15 illustrates Sumif.
Sumproduct and Sumif combined together
Cell G17 shows you how to build conditionality into Sumproduct, essentially combining Sumproduct and Sumif in one formula.
More on Sumproduct and Sumifs
Next we look at the ability of Sumifs to sort data over multiple criteria, as well as Sumproduct‘s ability to sort and amalgamate data.
About this online financial modelling course material
You’re looking at a free online course extract covering the most important Excel functions.
