How It Works

SUMIFS extends SUMIF to handle multiple conditions. All conditions must be met for a value to be included in the sum. This is essential for filtering by category AND date, or any multi-dimensional analysis.

Syntax

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

sum_range: The values to sum
criteria_range1: First range to check
criteria1: First condition
Additional criteria pairs as needed

Example

Monthly Category Total:

Sum all “Groceries” expenses in January 2025:

Date	Category	Amount
1/5/2025	Groceries	$85
1/12/2025	Dining	$45
1/15/2025	Groceries	$92
2/1/2025	Groceries	$78

Formula:

=SUMIFS(C:C, B:B, "Groceries", A:A, ">=1/1/2025", A:A, "<2/1/2025")

Result: $177 (only January groceries)

Common Scenarios

Expenses by Category and Month

=SUMIFS(Amounts, Categories, "Dining", Dates, ">="&DATE(2025,1,1), Dates, "<"&DATE(2025,2,1))

Income by Source and Year

=SUMIFS(Income, Source, "Salary", Year, 2025)

Transactions by Account and Type

=SUMIFS(Amounts, Account, "Checking", Type, "Expense")

Expenses Above a Threshold

=SUMIFS(Amounts, Categories, "Dining", Amounts, ">50")

Sums only dining expenses over $50.

Variations

With Cell References for Dates

=SUMIFS(C:C, B:B, E1, A:A, ">="&F1, A:A, "<"&G1)

Where E1=Category, F1=Start Date, G1=End Date

Using EOMONTH for Month Ranges

=SUMIFS(Amounts, Dates, ">="&D1, Dates, "<="&EOMONTH(D1,0))

Where D1 is the first of the month - sums entire month.

Wildcards for Partial Match

=SUMIFS(Amounts, Descriptions, "*grocery*", Categories, "Food")

Matches any description containing “grocery”.

Pro Tips

Order of ranges matters - sum_range comes first in SUMIFS (unlike SUMIF)
Date criteria need operators - use ”>=” and ”<” with dates
Combine with EOMONTH for clean month boundaries
Use named ranges for readability: =SUMIFS(Amounts, Categories, "Groceries", Months, "January")
Multiple criteria on same column - use the column twice for between/range conditions

Building a Monthly Summary

Create a summary table that auto-calculates:

Category	Jan	Feb	Mar
Groceries	=SUMIFS(…)	=SUMIFS(…)	=SUMIFS(…)
Dining	=SUMIFS(…)	=SUMIFS(…)	=SUMIFS(…)
Gas	=SUMIFS(…)	=SUMIFS(…)	=SUMIFS(…)

Formula pattern:

=SUMIFS($C:$C, $A:$A, $D2, $B:$B, ">="&E$1, $B:$B, "<"&F$1)

Using $ strategically allows copying across the table.

Common Errors

Error	Cause	Fix
0 (unexpected)	Criteria not matching	Check spelling, date formats
#VALUE!	Range size mismatch	All ranges must be same size
Wrong total	Date comparison issues	Use DATE() function for dates