SUMIF

Overview 

This is an extract from our pre-course videos for our renewable energy and infrastructure project finance modeling course.

Video

Video Transcript 

Many models may include calculations on a monthly or quarterly basis or perhaps even semi-annual, but outputs from a best practice standpoint are easier to understand on an annual basis. We can use the SUMIF function to calculate annual outputs based on semi-annual calculations.

And the SUMIF function is written as =SUMIF(range, criteria, sum_range).

To explain this, we will use an example shown on screen to determine the 2020 revenue in cell B6, based on semi-annual revenue in row 3.

We would type here =SUMIF($C$2:$F$2, B5.

This is, we are finding the criteria in B5, that’s the year 2020 within the range as the calendar year in C2 to F2. And make sure we anchor C2 to F2.

We want to sum all of the revenue within 2020 from C3 to F3, this is the sum range, so adding C3 to F3 anchored to the SUMIF, we get the full formula. And that full formula will sum all of the revenue in row 3,

IF (that’s SUMIF) the year is 2020, based on the criteria in B5. The answer is simply 10 plus 16 equals 26.

This one isn’t too hard, just make sure you anchor the correct references.

And as a general rule, when using SUMIF, you will most likely need to anchor your columns and probably also the rows.