SUMIFS and COUNTIFS: conditional totals that update themselves

When you need "total sales for the North location in September," SUMIFS is the tool. It adds up only the rows that meet every condition you set — and if you point the conditions at cells, the whole thing updates when you change them.

The syntax

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, …)

Note the order: the range you're summing comes first, then each condition as a range/criteria pair.

=SUMIFS(Amount, Location, "North", Date, ">="&A1)

That totals Amount where Location is North and Date is on or after the date in cell A1. COUNTIFS works identically but counts matching rows instead of summing:

=COUNTIFS(Location, "North", Status, "Open")

Point criteria at cells, not text

Hard-coding "North" works, but put the location in a cell and reference it ($A$1) and your summary becomes a mini-dashboard: change the cell, every total recalculates. Combine with a data-validation drop-down so you pick the location from a list.

Operators go in quotes, joined with &

Comparisons like greater-than or date ranges need quotes, and you join them to a cell with &: ">="&A1, "<"&B1, "<>Void". Forgetting the & is the most common SUMIFS error.

When a pivot is better

For one or two totals, SUMIFS is perfect and lives right next to your data. For slicing the same dataset many ways, a PivotTable is faster to build and reshape.

Building these totals by location every month across entities? That's what IMS Made Simple reports on automatically.