Lesson 9: SUMIFS: expenses by department and month

Practice exercises for accountants, checked instantly in your browser.

SUMIFS adds rows that meet several conditions. Its argument order differs from SUMIF: the range to add comes first, then pairs of range and criterion: =SUMIFS(amounts, depts, "Ops", months, "Jan").

In a summary grid with departments down column F and months across row 1, one formula can fill the whole table if the references are mixed: $F2 keeps the column but lets the row move, and G$1 keeps the row but lets the column move. This is the formula version of a PivotTable, and the same pattern works for any summary with labels down the side and across the top.

Worked example: =SUMIFS($D$2:$D$13,$B$2:$B$13,"Ops",$C$2:$C$13,"Jan")
Ops spending in January. The exercise replaces the typed text with mixed references.

Exercises

Exercise 1

Fernhill Bike Co. expense lines are in A2:D13. In G2, write one formula for the department in column F and the month in row 1 that fills the whole block G2:H4.

Exercise 2

Audit wants to know how many Ops expense lines are over 500. Answer in G6.