Google Sheets SUMIFS: How It Works and Examples

Yes. Google Sheets has a built-in SUMIFS function that adds numbers only when multiple conditions are met, such as summing sales for one region and one product. It works in Google Sheets in a web browser and is useful for reports, budgets, inventories, and other data arranged in rows and columns.

What SUMIFS Does in Google Sheets

SUMIFS checks one or more ranges for matching criteria, then adds the corresponding values from a separate sum range. For example, a sales sheet might contain a region, salesperson, product, and sales amount on each row. You can ask Sheets to total amounts where the region is East and the product is Widget, without filtering the table manually.

The key idea is that every condition supplied to a single SUMIFS must be true for a row to be included. In spreadsheet terms, the conditions work like AND: a row must match the first condition and the second condition and any additional conditions. Rows that fail even one test are left out of the total.

This makes the function useful for recurring calculations. Instead of selecting matching rows and adding them by hand, you can keep the formula in a summary cell and change its criteria as the underlying data grows or the reporting question changes.

Syntax and How Criteria Combine

The function takes a sum range first, followed by pairs of criteria ranges and criteria. In other words, you identify what to add, then say which range to check and what qualifies as a match. Add another range-and-criterion pair for each additional condition.

For a reliable result, use ranges with matching dimensions: the sum range and each criteria range should cover the same number of rows and columns. If the sum range covers rows 2 through 100 while a criteria range covers rows 2 through 99, the formula may return an error rather than a useful total. Keep the ranges aligned.

Criteria can be text, numbers, expressions such as greater than a value, wildcard patterns, or references to cells containing criteria. Text criteria should be enclosed in straight double quotation marks when typed directly into a formula. A criterion stored in a cell is referenced without quotation marks around the cell reference.

A Small Dataset and a First Formula

Suppose a table uses column A for dates, B for region, C for salesperson, D for product, and E for sales. Its six example records are: January 5, East, Ana, Widget, 120; January 12, West, Ben, Widget, 90; February 3, East, Ana, Gadget, 75; January 20, East, Ben, Widget, 60; February 10, West, Ana, Gadget, 110; and January 28, East, Ana, Widget, 45. The dates are in 2026 and the amounts are illustrative.

With the sample records in rows 2 through 7, the following formula adds sales only where column B is East and column D is Widget:

=SUMIFS(E2:E7,B2:B7,"East",D2:D7,"Widget")

It returns 225: the matching sales are 120, 60, and 45. The West Widget sale is excluded because its region does not match, and the East Gadget sale is excluded because its product does not match.

Criteria can instead refer to cells, which is helpful for a dashboard or a reusable summary. With the same data in rows 2 through 7, and with G2 containing East and G3 containing Widget, use:

=SUMIFS(E2:E7,B2:B7,G2,D2:D7,G3)

This also returns 225. Changing G2 to West or G3 to Gadget updates the total without editing the formula. Cell-driven criteria are especially convenient when other people will use the sheet and should choose conditions from a small input area.

Using Dates, Numbers, Text, and Wildcards

For comparisons, place the operator and value together inside a text criterion. For example, ">=100" means at least 100, while "<50" means less than 50. When the comparison value is in another cell, join the operator to that cell with the ampersand concatenation operator. This lets the condition change when the value in the cell changes.

With the sample data in rows 2 through 7, this formula totals East-region sales dated in January 2026:

=SUMIFS(E2:E7,B2:B7,"East",A2:A7,">="&DATE(2026,1,1),A2:A7,"<"&DATE(2026,2,1))

The result is 225: the East January amounts are 120, 60, and 45. Using a less-than February 1 boundary is a dependable way to include all January dates, including entries that may also contain times. The DATE function also avoids relying on how a date is displayed in the sheet.

Text criteria can use wildcards. An asterisk matches any sequence of characters, and a question mark matches one character. With the example records in rows 2 through 7, this sums every product name beginning with Wid:

=SUMIFS(E2:E7,D2:D7,"Wid*")

It returns 315, adding the Widget amounts 120, 90, 60, and 45. If a wildcard character itself needs to be matched literally, place a tilde before it. Wildcards are useful for product labels or names that share a predictable beginning, but they can also match more items than intended, so check the wording in the source data.

SUMIFS vs. SUMIF, FILTER, and QUERY

Use SUMIFS when you want one total based on multiple conditions. Google Sheets also has SUMIF, which is intended for a sum based on one condition. For example, a region-only total is simpler with SUMIF; when the question adds a product, date, or status condition, SUMIFS is the natural choice.

If the request is to show the matching rows rather than add their values, a filtering function is a better fit. FILTER can return records that meet conditions, which lets you inspect the included transactions or build a separate view. It does not replace a compact single-number total when the goal is just a sum.

QUERY is another option for more involved summaries, especially when you want grouped results or a table with several calculated outputs. It has its own query-language syntax, so it can be more than is needed for a simple total. Choose the smallest tool that clearly expresses the task: SUMIFS for conditional totals, FILTER for matching rows, and QUERY for query-style reporting.

One common misconception is that two alternative criteria in one SUMIFS automatically mean OR. They do not: criteria pairs in the same function are combined as AND. If you need East or West, you can calculate the two conditional totals separately and add them, or use another approach suited to the report. Be careful with overlapping conditions, because adding separate totals can count a row twice if it qualifies for both.

Common Errors and How to Fix Them

If a formula returns zero unexpectedly, first inspect the criteria values and the source data. A trailing space in a category such as East, a misspelled product, or a number stored as text can prevent an apparent match. Check a few source cells directly, and consider using a clean, consistent category list rather than manually retyping labels in multiple places.

Range sizes do not match: Make the sum range and every criteria range cover the same rows and columns.
Numbers are stored as text: Convert the source values to numbers if they should be added; text that looks numeric may not behave like a numeric amount.
Dates are text: Confirm that date entries are recognized as dates, not just strings that resemble dates.
Criteria operators are quoted incorrectly: Put a typed comparison such as greater than 100 in quotation marks, or join the operator to a cell reference.
Unexpected wildcard matches: Review whether an asterisk pattern is matching more labels than intended.

Google Sheets formulas normally use commas between arguments in many locales, as shown here. Depending on the spreadsheet’s locale, the argument separator may be a semicolon instead. If a pasted formula is rejected despite balanced parentheses and valid ranges, check the spreadsheet’s locale under File > Settings and use the separator accepted by that sheet. Locale can also affect how dates and decimal values are entered.

Making SUMIFS Easier to Maintain

Well-organized source data makes conditional totals easier to build and audit. Keep one record per row, use a clear header row, and avoid inserting subtotals or blank separator rows inside the data. Store dates as dates and amounts as numbers. Consistent labels reduce the chance that visually similar entries will be treated as different criteria.

For a growing table, you can use open-ended ranges such as E2:E and B2:B so newly added rows are included automatically. This is convenient, but very large sheets with many formulas may recalculate more work than necessary. For a modest data set, fixed ranges are easier to inspect; for a continuously updated log, open-ended ranges can reduce maintenance. Either way, keep the paired ranges aligned.

When a formula becomes difficult to read, build it incrementally. Start with one condition, confirm the total against a few visible records, and then add the next criteria pair. Give criteria cells clear labels, and place the formula near the summary it supports. This makes it easier for another person to understand what the number represents instead of treating it as a mysterious result.

Google Sheets also offers formula suggestions and an in-product function help panel when entering functions. These can help identify argument order, but the result should still be checked against the question being asked. In particular, decide whether conditions mean all requirements must match, or whether the report needs alternative matches, before choosing the formula structure.

Frequently Asked Questions

Is SUMIFS available in Google Sheets?
Yes. SUMIFS is a built-in Google Sheets function for adding values that meet multiple conditions. It can be entered directly into a cell or used as part of a larger formula.

What is the difference between SUMIF and SUMIFS?
SUMIF is designed for summing based on one condition. SUMIFS handles multiple conditions, with all criteria in one function needing to match for a row to count.

Can SUMIFS use dates as criteria?
Yes. Use date comparisons such as on or after a start date and before an end date. Building the dates with DATE can avoid ambiguity from different date display formats.

Can SUMIFS add values when either condition is true?
Not by treating separate criteria pairs in one function as OR; those pairs use AND logic. You can add separate conditional totals for alternatives, while checking for overlap so the same record is not counted twice.

Does the SUMIFS formula work in Excel too?
Excel also supports SUMIFS, and the basic argument order and examples are similar. Some spreadsheet settings, such as regional argument separators, can differ, so a formula may need commas or semicolons appropriate to the application and locale.

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.