SUMIFS Command in Google Sheets: Syntax and Examples

If sales amounts are in D2:D100 and the matching regions are in B2:B100, use =SUMIFS(D2:D100,B2:B100,"West") to add sales for the West region. In Google Sheets, SUMIFS totals values that meet one or more criteria, making it useful for questions such as “How much did this region sell for this product during this period?”

What SUMIFS Does

SUMIFS is a conditional addition function. It checks one or more ranges for specified conditions and adds the values from a separate sum range only when the corresponding row satisfies those conditions. It is a practical way to summarize a transaction list without manually filtering and adding individual records.

Each condition applies to its own criteria range. For example, you can require a row to have a particular region in one column and a particular product in another. Multiple conditions in one SUMIFS formula work as AND conditions: a row must meet every condition to contribute to the total. A row that matches the region but not the product is excluded.

The function does not change the source data. It returns a calculated total in the cell where you enter the formula, and it recalculates when relevant values change. That makes it suitable for summaries that need to stay current as a list grows or transactions are corrected.

SUMIFS Syntax and Argument Order

The general structure is SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...]). The first argument is the range of numbers to add. After it, provide a criteria range and its matching condition, then repeat that range-and-condition pair for any additional requirement. The square brackets in the reference notation mean optional arguments; do not type them into a formula.

sum_range: The cells containing amounts to total. Text and blank cells in this range are not added as numbers.
criteria_range1, criterion1: The cells to test and the rule those cells must meet.
Additional pairs: Optional tests, such as a date range, category, salesperson, or status.

Keep the ranges aligned: they should cover the same number of rows and columns so each amount is tested against the corresponding record. In a typical transaction table, use matching row boundaries, such as D2:D100 with B2:B100, rather than D2:D100 with B2:B80. Google Sheets may return an error when ranges have incompatible dimensions.

A Worked Example with Sales Data

Suppose a small table uses A for date, B for region, C for product, and D for sales. Its records are: row 2, Jan. 5, 2026, West, Trail Tent, 120; row 3, Jan. 8, East, Camp Stove, 80; row 4, Feb. 2, West, Camp Stove, 150; row 5, Feb. 14, West, Trail Tent, 90; and row 6, Feb. 20, East, Trail Pack, 200. Treat the sales figures as currency if appropriate, but the calculation itself just adds the numeric values.

With regions in B2:B6 and sales in D2:D6, enter =SUMIFS(D2:D6,B2:B6,"West"). The result is 360, from 120 + 150 + 90. This is the simplest useful case: one condition determines which amounts are included.

With regions in B2:B6, products in C2:C6, and sales in D2:D6, enter =SUMIFS(D2:D6,B2:B6,"West",C2:C6,"Trail Tent"). The result is 210, from the two West Trail Tent records, 120 + 90. The second condition narrows the qualifying rows; it does not create a separate total for products that fail the region test.

This same pattern works with longer lists. For instance, the sum range could cover thousands of sales rows, while each criteria range covers the corresponding region, product, or date column. Using bounded ranges can make a workbook easier to inspect; whole-column ranges can be convenient when new records are regularly appended. Whichever approach you choose, keep the ranges consistent.

Using Text, Number, Date, and Wildcard Criteria

A criterion can be an exact text value such as "West", a number, a comparison, or a reference to a cell containing a condition. Text matching is generally not case-sensitive, so capitalization alone does not distinguish “West” from “WEST.” If criteria come from a cell, referencing that cell makes a summary easier to update without editing the formula.

Comparison criteria use operators such as greater than, less than, or equal to. When the operator is combined with a value stored in another cell, join them with the ampersand character. For example, if a threshold is in F1 and amounts are in D2:D100, a criteria pair can test the values in D2:D100 against the threshold. This is useful for totals above a changing target, though make sure the tested range represents the quantity you actually intend to filter by.

Dates are stored as values in Sheets, so a reliable way to sum a period is to set a lower bound and an exclusive upper bound. With dates in A2:A6 and sales in D2:D6, enter =SUMIFS(D2:D6,A2:A6,">="&DATE(2026,2,1),A2:A6,"<"&DATE(2026,3,1)). The result is 440: the February records total 150 + 90 + 200. Using less than March 1 includes every time on February 28 if the date cells also contain times. The formula uses HTML-escaped ampersands and less-than signs in this article; when entering it in Sheets, type the ordinary characters & and < in those positions.

Wildcards can help when the text criterion is a pattern rather than one exact label. An asterisk matches a sequence of characters, while a question mark matches one character; a tilde can be used to treat a wildcard character literally. With product names in C2:C6 and sales in D2:D6, enter =SUMIFS(D2:D6,C2:C6,"Trail*"). The result is 410, from Trail Tent and Trail Pack records (120 + 90 + 200). Check that a broad pattern does not unintentionally include categories you meant to keep separate.

Entering the Function in Google Sheets

In a browser, select the cell for the total and type an equals sign followed by SUMIFS and an opening parenthesis. Add the sum range first, then each criteria range and its condition in pairs, and close the parenthesis. Press Enter to calculate. Google Sheets may display suggestions while you type; these can help identify the expected argument order, but check that each range points to the intended column.

For a formula that is easier to maintain, put a criterion such as West in a cell and refer to that cell instead of typing the text into the formula. If you copy the formula to another summary cell, consider whether the references should move. Add dollar signs to make a reference absolute when it must remain fixed during copying, such as $B$2:$B$100. Relative and absolute references affect copying, not the SUMIFS calculation itself.

Formulas here use commas between arguments. Google Sheets may use a different argument separator, such as semicolons, depending on the spreadsheet’s locale. If a formula with commas is rejected, check the spreadsheet locale in File > Settings and use the separator expected by that locale. This is a regional parsing difference, not a different SUMIFS method.

Common SUMIFS Problems and Fixes

The total is zero: Check for extra spaces, spelling differences, criteria that do not match the underlying values, and numbers or dates stored as text. A visually similar value may not be the same type of data.
The formula returns an error: Confirm that each criteria range has a corresponding criterion, parentheses are balanced, and the ranges have compatible dimensions. Also check whether your locale expects semicolons instead of commas.
The result is too high or too low: Verify the sum range and criteria ranges row by row. A shifted range can pair a sale with the wrong region or date. Check whether a wildcard or broad comparison includes more records than intended.
A date condition misses records: Make sure the cells contain real date values rather than text that only looks like a date. If timestamps are present, an exclusive next-period boundary is often safer than trying to match one exact date.
Adding another criterion removes expected rows: Remember that criteria pairs are combined with AND. Every qualifying row has to meet all of them.

When diagnosing a complicated formula, test one condition first and then add the next pair. If the one-condition total is already wrong, the issue is likely in that range or criterion; if it changes unexpectedly after adding a condition, inspect the new range and the AND logic. A temporary filter on the source table can also help you see which records should qualify, but the formula remains the reusable summary.

SUMIFS Compared with SUMIF and Other Options

Use SUMIF when you need to sum values based on one condition; its argument order differs because the criteria range comes first and the sum range is optional at the end. Use SUMIFS when you need multiple conditions. Even for one condition, SUMIFS can be a convenient choice if you expect to add more criteria later, provided you keep its sum-range-first argument order straight.

SUMIFS is not the same as a filter that displays matching rows. It returns a total, not a list of records. For an interactive overview across several categories, a pivot table may be more suitable; for a small one-off check, filtering and using a visible subtotal may be enough. Choose the method based on whether you need a formula-driven total, an exploratory summary, or the matching records themselves.

A common misconception is that multiple criteria in SUMIFS mean “this condition or that condition.” They normally mean all conditions must be true. For alternatives such as summing two mutually exclusive regions, use two separate SUMIFS calculations and add their results, or arrange the data and summary so each category can be evaluated clearly. If a record could meet both alternatives, adding two totals may count it twice, so consider the logic before combining results.

Tips for Reliable, Maintainable Formulas

Good source data makes conditional sums more dependable. Keep one record per row, use consistent category names, store amounts as numbers, and store dates as actual dates. Avoid adding subtotals inside the data range unless they are intentionally part of the calculation; otherwise, the same sales may be counted twice.

Use a header row and keep headers out of the ranges being summed or tested.
Use cell references for changing thresholds, category names, or date boundaries.
Apply the same start and end rows to the sum range and every criteria range.
Test the result against a small filtered sample when the total affects an important report.

Google Sheets and Excel both offer SUMIFS with the same general sum-range-first structure, so basic formulas are often portable between them. Differences can still arise from locale-specific separators, data formatting, or workbook settings. If a formula is being moved between programs, check how dates and imported text values are stored rather than assuming that a displayed value guarantees an identical underlying value.

Frequently Asked Questions

What does SUMIFS mean in Google Sheets?

SUMIFS means that Sheets sums values only when their corresponding records satisfy one or more criteria. The “IFS” part reflects that multiple tests can be included; those tests are combined as AND conditions.

What is the difference between SUMIF and SUMIFS?

SUMIF is designed for one condition, while SUMIFS can apply multiple conditions. SUMIF places the criteria range before its sum range; SUMIFS starts with the sum range, so do not copy the arguments in the wrong order.

Can SUMIFS use two conditions?

Yes. Add a criteria-range-and-criterion pair for each condition after the sum range. For example, a formula can require both a region and a product to match before adding a sale.

Why does SUMIFS return zero when matching rows are visible?

Check that the criteria exactly match the stored values and that number and date criteria use the correct data types. Hidden spaces, text-formatted numbers, mismatched date values, or an incorrect range can prevent an apparent match.

Can SUMIFS add values between two dates?

Yes. Use the date column as a criteria range twice: once for a start-date condition and once for an end-date condition. A less-than condition on the first day of the next period is useful when cells might include timestamps.

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.