Sumif and Sumproduct formulas

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.

Excel's Sumproduct and Sumif functions

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.

 

 

...join the conversation...

Your email address will not be published. Required fields are marked *