Select Page

For as long as I can remember Excel has included a SUMIF function which is used to add up the numbers in a range of cells that match a single criteria.

Being a Manchester United fan, I might need to know how many goals were scored by the team at home matches.

Using the example above, assuming that the data was in rows 2:39, I could use the following formula: =SUMIF(C2:C39,”Home”,E2:E39)

In 2007 Microsoft introduced the SUMIFS function into Excel. It was SUMIF but with multiple criteria so for example I could use it to calculate how many goals were scored where the match was played at home and the team won and the match was played on a Sunday.

=SUMIFS(E2:E39,C2:C39,”Home”,D2:D39,”W”,B2:B39,”Sun”)

For many people, the logic as to which function to use is “One criteria…use SUMIF. Multiple criteria…use SUMIFS” and whilst that may be true, SUMIFS can handle both single and multiple criteria so there really is no need to use SUMIF any more. Watch the video to see it in action.