How To SheetsHow To Sheets
Formulas and Functions

Sumifs in Google Sheets: A Practical Guide

Master SUMIFS in Google Sheets to sum values with multiple criteria. Learn syntax, examples, tips, and common pitfalls for students, professionals, and small businesses.

How To Sheets
How To Sheets Team
·5 min read
Charts-Google-SheetsWhat-Is-Google-SheetsCount-Data-Google-SheetsFormula-Errors-Google-Sheets
Sumifs in Sheets - How To Sheets
Quick AnswerDefinition

SUMIFS in Google Sheets sums values based on multiple criteria. The syntax is SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2], ...). All ranges must be the same size. For example, =SUMIFS(Sales!C:C, Sales!A:A, "Widget", Sales!B:B, "East"). It is ideal for filtering by product, region, date, or category without adding extra columns and supports wildcards for text criteria. sumifs google sheets

What SUMIFS does and when to use it

SUMIFS is a multi-criteria variant of SUMIF. It calculates the total of values in sum_range that meet every specified criterion. This makes it ideal for dashboards, financial summaries, inventory reports, and analysis that requires filtering by attributes such as product, region, date, or category. Understanding SUMIFS is essential for building responsive spreadsheets where you want to slice data without adding helper columns.

Excel Formula
=SUMIFS(Sales!C:C, Sales!A:A, "Widget", Sales!B:B, "East")

Parameters:

  • sum_range: the column with numeric values to sum (e.g., Sales!C:C)
  • criteria_range1: the first column to test (e.g., Sales!A:A)
  • criterion1: the condition to apply ("Widget")
  • criteria_range2/criterion2: additional filters (Sales!B:B, "East")

You can extend with more criteria, e.g., date or status. For text criteria, use wildcards like "Widget*" or "Widget". For numbers, use operators like ">=100" or "<75". When using date criteria, concatenate with operators, e.g., ">="&DATE(2026,1,1).

blockNoteIgnoredQuotesAreEscaped

Steps

Estimated time: 15-25 minutes

  1. 1

    Plan your data layout

    Map out which column holds the values to sum (sum_range) and which columns will act as criteria (criteria_range1, criteria_range2, etc.). Decide how you will label headers and where to place helper columns if needed.

    Tip: Create a small sample tab with headers to validate your formulas before applying them to production data.
  2. 2

    Write a simple SUMIFS formula

    Start with a single criterion to verify that the basic mechanism works. Use a static value for the first run to validate results against a manual subtotal.

    Tip: Keep the sum_range numeric and ensure the criterion column contains compatible data types.
  3. 3

    Add additional criteria

    Extend the formula by adding criteria_range2 and criterion2. Confirm that all ranges align in length and that each criterion logically applies to the corresponding range.

    Tip: Use cell references for criteria to make the formula dynamic.
  4. 4

    Validate results

    Cross-check a few test cases against manual totals or a PivotTable. Confirm edge cases, such as empty results and exact vs. wildcard matches.

    Tip: Edge-case testing helps catch off-by-one or data-type issues early.
  5. 5

    Scale to real dashboards

    Replace hard-coded values with named ranges or cell references, and consider combining SUMIFS with other functions for dynamic visuals.

    Tip: Document the formula and consider adding comments in a separate sheet for teammates.
Pro Tip: Use named ranges to simplify SUMIFS and improve readability.
Warning: Mismatched range sizes will yield errors; always ensure all criteria ranges align with the sum_range.
Note: Wildcards like '*' enable flexible text matching in criteria.
Pro Tip: Combine SUMIFS with FILTER or QUERY to build dynamic dashboards.

Prerequisites

Required

  • Google account with access to Google Sheets
    Required
  • Basic spreadsheet skills (SUM, IF, and simple functions)
    Required

Optional

  • Knowledge of data organization (columns for sum_range and criteria)
    Optional
  • Optional: a sample test dataset to practice on
    Optional

Keyboard Shortcuts

ActionShortcut
CopyCopy selected cellsCtrl+C
PastePaste formulas or valuesCtrl+V
Paste values onlyPaste without formattingCtrl+⇧+V
Toggle absolute/relative references in a formulaCycle through $A$1, A$1, $A1, A1F4
Find textSearch within the sheetCtrl+F

FAQ

What is SUMIFS and how does it differ from SUMIF?

SUMIFS sums values using multiple criteria, while SUMIF handles a single condition. SUMIFS is ideal for multi-dimensional filtering in reports and dashboards.

SUMIFS lets you add several conditions, unlike SUMIF which handles one rule. This helps build multi-criteria reports faster.

Can SUMIFS handle dates and date ranges?

Yes. Use date comparisons like >= or <= with DATE or DATEVALUE. For ranges, apply two criteria on the date column, one for the start and one for the end.

Absolutely. You can filter by date ranges by applying two date criteria in SUMIFS.

Why do I see #VALUE! or #NAME? errors with SUMIFS?

Common causes are mismatched range sizes, non-numeric sum_range, or misspelled range names. Double-check your ranges and data types.

Usually it’s a range size mismatch or non-numeric data. Fix the ranges and types and the error clears up.

Can I use SUMIFS with named ranges?

Yes. SUMIFS works with named ranges just like with standard ranges, improving readability and maintainability.

Absolutely—named ranges make SUMIFS formulas much easier to read.

Is SUMIFS available on mobile Google Sheets?

Yes, SUMIFS is available in Google Sheets mobile apps, though complex formulas may be harder to compose on small screens.

You can use SUMIFS on mobile, but editing complex rules is easier on a computer.

The Essentials

  • Sum with multiple criteria using SUMIFS
  • Align sum_range and criteria ranges precisely
  • Use named ranges for readability
  • Apply wildcards for flexible text criteria

Related Articles

← More in Formulas and Functions