How is SUMIFS different from SUMIF?

SUMIF applies one condition to one criteria range; SUMIFS applies multiple conditions to multiple ranges. Use SUMIF when you have a single criterion, and SUMIFS when you need two or more independent filters that must all be true.

Can I use wildcards like * in SUMIFS criteria?

Yes. For text criteria, wildcards work: 'Sales*' matches departments starting with 'Sales', and '?' matches a single character. Wildcards don't work with numeric comparisons like '>80000'.

What if I need OR logic instead of AND?

SUMIFS always uses AND logic. For OR, call SUMIF multiple times with different criteria and add the results, or use SUMPRODUCT: =SUMPRODUCT(((B2:B8='Sales')+(B2:B8='HR'))*(F2:F8='Active')*E2:E8).

Why do I get #VALUE! even though my formula looks correct?

The most common cause is a mismatch in range sizes. If sum_range is E2:E8 (7 rows) but criteria_range1 is B2:B9 (8 rows), Excel throws #VALUE!. Ensure all ranges are exactly the same height.

SUMIFS function

SUMIFS returns the sum of cells that meet multiple criteria, making it ideal for aggregating data with several conditions at once.

=SUMIFS(sum_range, criteria_range1, criteria1, ...)

Generate a SUMIFS formula

Describe what you need. The generator will reach for SUMIFS where SUMIFS is the right tool, and tell you when it is not.

Arguments

How SUMIFS reads its arguments

Argument	Required	Description
sum_range	Required	The range of cells to add up; must be the same height/width as each criteria_range, or Excel returns #VALUE!.
criteria_range1	Required	The first range to test; paired with criteria1. Can be text, dates, or numbers.
criteria1	Required	The condition applied to criteria_range1: e.g. 'Sales', '>50000', '>=2021-01-01'. Text criteria can use wildcards (* and ?).
...	Repeating	Additional criteria_range/criteria pairs (up to 127 pairs in Excel); all must be true simultaneously (AND logic).

Returns

A number representing the sum of all cells in sum_range that simultaneously satisfy all given criteria.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Sum active employees' salaries by department

ID	Name	Department	Hire Date	Salary	Status
1001	Alice Johnson	Sales	2020-03-15	65000	Active
1002	Bob Smith	Engineering	2019-07-22	85000	Active
1003	Carol White	Sales	2021-01-10	58000	Active
1004	David Brown	Engineering	2018-11-05	92000	Active
1005	Emma Davis	HR	2022-05-18	55000	Active
1006	Frank Miller	Sales	2020-08-30	62000	Inactive
1007	Grace Lee	Engineering	2021-02-14	78000	Active

=SUMIFS(E2:E8,B2:B8,"Sales",F2:F8,"Active")

Result: 123000

SUMIFS checks two conditions: Department='Sales' and Status='Active'. Alice Johnson ($65,000) and Carol White ($58,000) match both; Frank Miller is Sales but Inactive, so he's excluded. The sum is $123,000.

2. Sum salaries for recent hires with active status

ID	Name	Department	Hire Date	Salary	Status
1001	Alice Johnson	Sales	2020-03-15	65000	Active
1002	Bob Smith	Engineering	2019-07-22	85000	Active
1003	Carol White	Sales	2021-01-10	58000	Active
1004	David Brown	Engineering	2018-11-05	92000	Active
1005	Emma Davis	HR	2022-05-18	55000	Active
1006	Frank Miller	Sales	2020-08-30	62000	Inactive
1007	Grace Lee	Engineering	2021-02-14	78000	Active

=SUMIFS(E2:E8,D2:D8,">2020-12-31",F2:F8,"Active")

Result: 191000

This formula sums salaries where Hire Date is after 2020-12-31 AND Status is 'Active'. Carol (hired 2021-01-10, $58,000), Emma (hired 2022-05-18, $55,000), and Grace (hired 2021-02-14, $78,000) all qualify. Frank Miller is inactive, so despite being hired in 2020, he's excluded. Total: $191,000.

3. Sum high-earning Engineering salaries

ID	Name	Department	Hire Date	Salary	Status
1001	Alice Johnson	Sales	2020-03-15	65000	Active
1002	Bob Smith	Engineering	2019-07-22	85000	Active
1003	Carol White	Sales	2021-01-10	58000	Active
1004	David Brown	Engineering	2018-11-05	92000	Active
1005	Emma Davis	HR	2022-05-18	55000	Active
1006	Frank Miller	Sales	2020-08-30	62000	Inactive
1007	Grace Lee	Engineering	2021-02-14	78000	Active

=SUMIFS(E2:E8,B2:B8,"Engineering",E2:E8,">80000")

Result: 177000

This formula sums salaries where Department='Engineering' AND Salary>80000. Bob Smith ($85,000) and David Brown ($92,000) meet both criteria. Grace Lee is in Engineering but earns only $78,000, so she's excluded. The result is $177,000.

Common errors

Which SUMIFS error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	criteria_range and sum_range have mismatched dimensions (different row counts or column widths).	Verify that all ranges span the same number of rows: if sum_range is E2:E8 (7 rows), criteria_range1 must also be 7 rows, e.g., B2:B8, not B2:B9 or B1:B7.
#REF!	A referenced range has been deleted, moved, or contains an invalid reference syntax.	Check that all range addresses exist and are spelled correctly. If a column was deleted, update the formula to reference the correct columns.
#NAME?	SUMIFS is misspelled, or a named range used in criteria no longer exists.	Verify the function name is spelled 'SUMIFS' (not 'SUMIF'). If using a named range, ensure it exists in the workbook and hasn't been deleted.

Tips and when to use something else

Use AND logic by default: all criteria must be true. For OR logic ('Department=Sales OR Department=HR'), use two SUMIF calls or SUMPRODUCT.
Text criteria accepts wildcards: 'Sales*' matches 'Sales', 'Sales East', 'Sales Manager'; '?' matches any single character.
Criteria ranges must be the same size as sum_range. A common mistake is using B2:B7 when your data goes to B8.
For complex logic or when ranges are non-contiguous, SUMPRODUCT is more flexible: =SUMPRODUCT((B2:B8='Sales')*(F2:F8='Active')*E2:E8).

Related functions

SUMIFSUMIF returns the sum of cells in a range that meet a specified criteria, useful for totaling data filtered by a single condition.SUMPRODUCTSUMPRODUCT multiplies arrays element-by-element and sums the results, useful for weighted totals and multi-criteria counting.IFThe IF function returns one value if a condition is true and another (or nothing) if it is false, letting you branch logic in a cell.

Frequently asked questions

How is SUMIFS different from SUMIF?: SUMIF applies one condition to one criteria range; SUMIFS applies multiple conditions to multiple ranges. Use SUMIF when you have a single criterion, and SUMIFS when you need two or more independent filters that must all be true.
Can I use wildcards like * in SUMIFS criteria?: Yes. For text criteria, wildcards work: 'Sales*' matches departments starting with 'Sales', and '?' matches a single character. Wildcards don't work with numeric comparisons like '>80000'.
What if I need OR logic instead of AND?: SUMIFS always uses AND logic. For OR, call SUMIF multiple times with different criteria and add the results, or use SUMPRODUCT: =SUMPRODUCT(((B2:B8='Sales')+(B2:B8='HR'))*(F2:F8='Active')*E2:E8).
Why do I get #VALUE! even though my formula looks correct?: The most common cause is a mismatch in range sizes. If sum_range is E2:E8 (7 rows) but criteria_range1 is B2:B9 (8 rows), Excel throws #VALUE!. Ensure all ranges are exactly the same height.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17