Multiple SUMIFS in Google Sheets: Criteria and Examples

Yes. In Google Sheets, use one SUMIFS for conditions that must all be true, and add separate SUMIFS results when you want to include different condition groups. For example, to total Hardware sales in the East and West regions, add one regional sum to the other; the sections below explain the syntax, examples, and important overlap pitfalls.

What “multiple SUMIFS” can mean

People use “multiple SUMIFS” to describe two different tasks. The first is adding several conditions to a single SUMIFS: for instance, sum sales where the region is East and the category is Hardware. The second is combining multiple SUMIFS calculations, often because the desired result includes one condition group or another. Knowing which kind you need determines the formula.

SUMIFS has a sum range followed by one or more pairs of criteria ranges and criteria. Each pair tests the same row, and all pairs in one function must be satisfied for that row’s value to be included. This is an AND relationship. To create an OR relationship—such as East or West—you can add separate calculations, as long as you account for rows that might qualify in more than one calculation.

Assume a sheet has Region in A, Category in B, Date in C, and Amount in D, with headers in row 1 and records in rows 2 through 7:

East, Hardware, Jan 5 2026, 120
West, Hardware, Jan 12 2026, 80
East, Software, Jan 20 2026, 200
East, Hardware, Feb 2 2026, 60
West, Hardware, Jan 30 2026, 40
East, Hardware, Jan 25 2026, 30

The values are illustrative. This small dataset makes it possible to check each result by adding the matching amounts manually.

Use one SUMIFS for AND conditions

When the conditions must all be true, keep them together in one function. With region in A2:A7, category in B2:B7, and amounts in D2:D7, this formula sums East-region Hardware rows:

With regions in A2:A7, categories in B2:B7, and amounts in D2:D7, enter =SUMIFS(D2:D7,A2:A7,"East",B2:B7,"Hardware"). It returns 210: the matching amounts are 120, 60, and 30.

The argument order matters: first comes the range to add, then a criteria range and its condition, followed by any further range-condition pairs. The criteria range must correspond row by row with the sum range. If row 4 in the criteria ranges refers to one transaction, row 4 in the sum range must refer to that same transaction.

More conditions can be appended in pairs. For example, add a date range and a date condition to require East, Hardware, and a date in January. Google Sheets requires each added criteria range to have the same dimensions as the sum range. A mismatch can cause an error or a misleading result, so use aligned ranges such as D2:D100, A2:A100, and B2:B100 rather than ranges that start or end on different rows.

Add separate SUMIFS results for OR conditions

If either East or West should qualify, a practical approach is to calculate each group separately and add the results. With the example data, the East Hardware total is 210 and the West Hardware total is 120, so the combined result is 330.

With regions in A2:A7, categories in B2:B7, and amounts in D2:D7, enter =SUMIFS(D2:D7,A2:A7,"East",B2:B7,"Hardware")+SUMIFS(D2:D7,A2:A7,"West",B2:B7,"Hardware"). It returns 330, the sum of all five Hardware entries.

This works cleanly because a row cannot be both in the East region and in the West region, so no row is counted twice. The same pattern suits alternatives such as “status is Paid or Refunded” when those statuses are mutually exclusive.

However, adding results can overcount if the groups overlap. Suppose the request is “Hardware or any sale over 100.” A Hardware transaction over 100 meets both conditions; adding a Hardware total to a greater-than-100 total counts that transaction twice. Before adding separate sums, check whether any record can satisfy more than one group. If groups overlap, define non-overlapping groups, or choose a formula designed to count each qualifying row once.

Combine dates, numbers, and text criteria

SUMIFS can compare dates and numbers as well as match text. For a date interval, use a lower bound inclusive and the next period’s start as an exclusive upper bound. This avoids having to guess the final time value if a date column includes timestamps.

With dates in C2:C7, regions in A2:A7, categories in B2:B7, and amounts in D2:D7, enter =SUMIFS(D2:D7,A2:A7,"East",B2:B7,"Hardware",C2:C7,">="&DATE(2026,1,1),C2:C7,"<"&DATE(2026,2,1)). It returns 150, from the January East Hardware records of 120 and 30.

The ampersand joins a comparison operator to a date value. In this example, DATE(2026,1,1) creates January 1, 2026, and the second condition excludes February 1 and later. Use real date values in the sheet, not text that only looks like a date; text dates may not compare as expected.

For a numeric threshold, comparison operators such as >, >=, <, and <= can be used as criteria. With amounts in D2:D7 as both the sum range and the criteria range, =SUMIFS(D2:D7,D2:D7,">100") returns 320, because 120 and 200 are the only amounts greater than 100. When entering criteria through cell references, join the operator and reference—for example, ">"&F2—rather than putting the cell address inside a quoted text criterion.

Use cell references and wildcards

Putting conditions in cells makes a report easier to reuse. A reader can change the selected region or category without editing the formula. With regions in A2:A100, categories in B2:B100, amounts in D2:D100, the chosen region in F2, and the chosen category in G2, enter =SUMIFS(D2:D100,A2:A100,F2,B2:B100,G2). The result changes when either selector cell changes.

Text criteria can also use wildcards. An asterisk matches any number of characters, including none, and a question mark matches one character. With categories in B2:B7 and amounts in D2:D7, =SUMIFS(D2:D7,B2:B7,"*ware") returns 530 for the example data: both Hardware and Software end in “ware.” Wildcards are useful for consistent naming patterns, but broad patterns can match more rows than intended. If a literal asterisk or question mark is part of the text to match, Google Sheets uses a tilde to escape wildcard characters.

Criteria text normally matches the cell’s text rather than searching for a substring. Use a wildcard pattern when a partial match is intended. Also remember that labels with extra spaces or inconsistent spelling may not match a criterion that looks nearly identical; cleaning the source values can be more reliable than making a complicated formula.

Keep ranges aligned and results understandable

For a formula that will be copied down or extended as data grows, choose a range strategy deliberately. Fixed ranges such as D2:D1000 can be simple, but new records below the endpoint are ignored. Whole-column references such as D:D are convenient in Google Sheets, though they can make a large or complex workbook slower. A table-like data block with consistent columns and a planned endpoint is often a good compromise.

Use the same start and end rows for every range in a SUMIFS. Check that numbers are stored as numbers, dates as dates, and category labels are consistent. A value imported as text may look numeric but fail to behave like a number in a calculation. If a total is zero, inspect a few individual rows and verify both their source values and every condition.

For reports, descriptive labels can prevent confusion. A result titled “East Hardware, January” is clearer than a bare total, especially when a formula contains several criteria. If a spreadsheet uses a locale that requires semicolons instead of commas between arguments, follow the separators accepted by that sheet; the examples here use the common comma-separated syntax.

Common problems and useful alternatives

Wrong total: Confirm that every criteria range lines up with the sum range and that the condition represents the intended AND or OR logic.
Zero result: Check for misspellings, leading or trailing spaces, text-formatted dates, and numbers stored as text.
Overcount: If multiple SUMIFS are added, test whether a row can qualify in more than one group.
Missing new records: Expand fixed ranges or use a range design that includes future entries.
Unexpected partial matches: Remove wildcards if you need an exact text match, or make the wildcard pattern more specific.

A pivot table can be a better alternative when the goal is to summarize many categories and regions at once rather than calculate one tailored total. In Google Sheets, choose Insert > Pivot table and set the source range, then use the Pivot table editor to add rows, columns, values, and filters. For a small number of criteria and a total that must appear in a particular cell, SUMIFS is usually more direct. A filter can help inspect matching records, but it does not by itself replace a formula-driven total.

Google Sheets and Excel both provide SUMIFS, and the core AND logic and argument order are similar. The examples here are written for Google Sheets in a web browser; Excel may differ in its handling of array-based criteria expressions and in interface details. The separate-formula approach shown above avoids depending on array-criteria behavior and makes each OR group easy to review.

Frequently Asked Questions

Can SUMIFS use more than one condition?
Yes. Add a criteria-range and criterion pair for every condition that must be true. The pairs in one SUMIFS are combined as AND conditions.

How do I use OR with SUMIFS in Google Sheets?
Add separate SUMIFS calculations for each alternative, then add their results. First check that the alternatives do not overlap; otherwise, a qualifying row may be counted more than once.

Can I use a date range in SUMIFS?
Yes. Add two conditions on the date range, one for the start and one for the end. For a month, an inclusive first day and exclusive first day of the next month is a reliable pattern.

Why does my SUMIFS formula return zero?
Common causes include criteria that do not exactly match the data, mismatched range boundaries, dates or amounts stored as text, and unnoticed extra spaces. Check a record against each condition separately.

Can the criteria come from cells?
Yes. Put the desired values in cells and refer to those cells as criteria. For a comparison such as greater than a threshold in F2, join the operator to the reference with an ampersand.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.