Excel function
SUMIFS in Excel: sum values with multiple criteria
SUMIFS is the workhorse formula for answering questions like: how much revenue came from this region, this product, and this month? It turns a raw table into a focused total.
What SUMIFS does
SUMIFS adds numbers from one range only when every condition you provide is true. The sum range comes first, followed by pairs of criteria ranges and criteria.
The important mental model is filtering before adding: Excel checks each row against your conditions, keeps the matching rows, and sums the values from those rows.
Why SUMIFS matters
Most business spreadsheets are lists: transactions, orders, support tickets, invoices, or inventory movements. SUMIFS lets you turn those lists into answers without manually sorting or copying rows.
It is especially useful before you are ready to build a pivot table, or when you need a formula-driven dashboard that updates as source data changes.
Practical examples
Sum revenue for one region
=SUMIFS(D:D,B:B,"North")Adds values in column D only for rows where column B is North.
Sum revenue for region and status
=SUMIFS(D:D,B:B,"North",C:C,"Paid")Adds revenue where both conditions are true on the same row.
Recommended Excel functions
Common mistakes
- 01Putting the criteria range before the sum range. In SUMIFS, the sum range is first.
- 02Using ranges with different sizes, which can return errors or unreliable results.
- 03Forgetting quotation marks around text criteria such as "North" or "Paid".
Practice
Practice this skill with interactive exercises on Calcu.
Use Calcu to practice SUMIFS in realistic spreadsheet exercises with instant feedback.
FAQ
What is the difference between SUMIF and SUMIFS?
SUMIF handles one condition. SUMIFS handles multiple conditions and is usually the better default for real tables.
Can SUMIFS use dates?
Yes. Date criteria usually use comparison operators such as ">=" and "<=" combined with date cells.