# Google Sheets SUMIFS Function: Syntax, Examples & Tips - URL: https://betteranalyst.com/google-sheets/sumifs-function - Author: Better Analyst Formula Bot is now Better Analyst—same account, more powerful analysis.Formula Bot is now Better Analyst—same account and workflows, with more powerful analysis, dashboards, and automation. [Learn what changed](https://betteranalyst.com/formula-bot-is-now-better-analyst) Adds up values in a range that meet multiple conditions. ``` =SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...]) ``` Generate FormulaExplain Formula One free generation or explanation per browser, shared across Excel and Google Sheets. Account usage limits apply afterward. Describe the SUMIFS you need in plain EnglishGenerate Formula ## Parameters | Parameter | Required | Description | | - | - | - | | **sum\_range** | Yes | The range of cells to sum. | | **criteria\_range1** | Yes | The first range to evaluate against a criterion. | | **criterion1** | Yes | The condition for **criteria\_range1**. Can be a number, text, expression, or cell reference. | | **criteria\_range2, criterion2, ...** | No | Additional range/criterion pairs. Up to 127 pairs are supported. | ## What Is SUMIFS? SUMIFS is the multi-condition version of SUMIF. It sums values in a range where **all** specified conditions are met simultaneously. If you need to filter by product **and** region **and** date range, SUMIFS is your function. ## How SUMIFS Works SUMIFS checks each row against every criterion you provide. A value in **sum\_range** is included in the total only when **all** corresponding criteria are satisfied. It uses AND logic — every condition must be true. ## Key Difference from SUMIF In SUMIFS, the **sum\_range comes first**, followed by the criteria pairs. This is the opposite order from SUMIF: ``` SUMIF: =SUMIF(criteria_range, criterion, sum_range) SUMIFS: =SUMIFS(sum_range, criteria_range1, criterion1, ...) ``` ## Basic Examples ### Sum with two conditions ``` =SUMIFS(C2:C100, A2:A100, "Widget", B2:B100, "East") ``` Sums values in column C where column A is "Widget" **and** column B is "East". ### Sum within a date range ``` =SUMIFS(D2:D100, E2:E100, ">="&DATE(2025,1,1), E2:E100, "<="&DATE(2025,12,31)) ``` Sums values in column D where column E falls within the year 2025. ### Sum with a numeric threshold and text condition ``` =SUMIFS(C2:C100, A2:A100, "Premium", C2:C100, ">1000") ``` Sums values in column C where column A is "Premium" **and** the value itself exceeds 1000. ## Using Cell References as Criteria ``` =SUMIFS(D2:D100, A2:A100, G1, B2:B100, G2) ``` Sums column D where column A matches cell G1 and column B matches cell G2. This makes your formula dynamic — change G1 or G2 to update the result instantly. ## Using Comparison Operators All standard operators work the same as in SUMIF: | Operator | Example Criterion | | - | - | | Greater than | `">500"` | | Less than | `"<100"` | | Greater than or equal | `">="&D1` | | Less than or equal | `"<="&TODAY()` | | Not equal to | `"<>Cancelled"` | ## Using Wildcards SUMIFS supports the same wildcards as SUMIF: - `*` — matches any sequence of characters - `?` — matches any single character ``` =SUMIFS(C2:C100, A2:A100, "Pro*", B2:B100, "Active") ``` Sums where column A starts with "Pro" and column B is "Active". ## Advanced Examples ### Sum sales by rep in a specific month ``` =SUMIFS(D2:D500, B2:B500, "Sarah", C2:C500, ">="&DATE(2025,3,1), C2:C500, "<"&DATE(2025,4,1)) ``` ### Sum excluding a specific category ``` =SUMIFS(C2:C100, A2:A100, "<>Returns") ``` Sums all values in C where A is anything except "Returns". ### Sum with three conditions ``` =SUMIFS(E2:E100, A2:A100, "Laptop", B2:B100, "Online", D2:D100, ">=4") ``` Sums column E where product is "Laptop", channel is "Online", and rating is 4 or higher. ## Common Use Cases - **Sales dashboards**: Sum revenue by product + region + time period - **Budget analysis**: Total expenses by department + quarter - **HR**: Sum hours worked by employee + project + month - **Marketing**: Sum conversions by campaign + channel - **Inventory**: Sum stock by warehouse + product category ## Tips and Best Practices - All **criteria\_ranges must be the same size** as **sum\_range** — same number of rows. - SUMIFS uses **AND logic** — all conditions must be met. For OR logic, use multiple SUMIFS added together or use SUMPRODUCT. - Text criteria are **not case-sensitive**. - For date criteria, always use the `DATE()` function or a cell reference — avoid typing dates as text. - Use **named ranges** for readability in complex formulas. - When building dashboards, reference criteria cells instead of hardcoding values. ## OR Logic with SUMIFS SUMIFS only supports AND logic natively. To get OR logic, add multiple SUMIFS: ``` =SUMIFS(C:C, A:A, "Apples") + SUMIFS(C:C, A:A, "Oranges") ``` Or use SUMPRODUCT for more flexibility: ``` =SUMPRODUCT((A2:A100="Apples")+(A2:A100="Oranges"), C2:C100) ``` ## Common Errors - **Mismatched range sizes**: All ranges must have the same number of rows. `=SUMIFS(C2:C100, A2:A50, "X")` will error. - **#VALUE! error**: Often caused by using a range from a different sheet with incorrect syntax. - **Unexpected zero**: Usually means no rows match all conditions — double-check your criteria. - **Forgetting the argument order**: Remember sum\_range comes **first** in SUMIFS, unlike SUMIF. ## Example use case A regional sales manager needs to see Q1 revenue for the "Enterprise" segment in the "West" region. Using SUMIFS, they type =SUMIFS(E:E, B:B, "Enterprise", C:C, "West", D:D, ">="\&DATE(2025,1,1), D:D, "<"\&DATE(2025,4,1)) to get the exact figure without any manual filtering. ## Need more help with Google Sheets formulas? Sign up for Better Analyst and get unlimited AI-powered formula generation, explanations, and advanced data analysis. [Create Free Account](https://betteranalyst.com/signup)[Try Formula Generator](https://betteranalyst.com/excel-ai) ## Google Sheets how-to guides - [How To Unhide Columns In Google Sheets](https://betteranalyst.com/google-sheets/how-to-unhide-all-columns-in-google-sheets) - [How To Unhide Rows In Google Sheets](https://betteranalyst.com/google-sheets/how-to-unhide-all-rows-in-google-sheets) - [How To Unprotect Google Sheets](https://betteranalyst.com/google-sheets/how-to-unprotect-google-sheets) - [How To Wrap Text In Google Sheets](https://betteranalyst.com/google-sheets/how-to-wrap-text-in-google-sheets) - [Google Sheets Conditional Formatting](https://betteranalyst.com/google-sheets/google-sheets-conditional-formatting) - [How To Add A Column In Google Sheets](https://betteranalyst.com/google-sheets/how-to-add-a-column-in-google-sheets) - [How To Alphabetize In Google Sheets](https://betteranalyst.com/google-sheets/how-to-alphabetize-in-google-sheets) - [How To Autofit In Google Sheets](https://betteranalyst.com/google-sheets/how-to-autofit-in-google-sheets) ## Explore all Google Sheets formulas Browse our complete library of Google Sheets formula guides Compare your options ## Decide with evidence, not a sales pitch. Open a neutral research prompt in the AI tool you already use. Compare capabilities, limitations, and fit before you choose. 1. [01ChatGPTOpen a balanced comparison prompt in ChatGPT.](https://chatgpt.com/?prompt=Compare%20Better%20Analyst%20with%20using%20a%20general-purpose%20AI%20assistant%20for%20data%20analysis.%20Evaluate%20spreadsheet%20and%20document%20analysis%2C%20dashboards%2C%20repeatable%20reports%2C%20code%20transparency%2C%20data%20connections%2C%20pricing%2C%20and%20limitations.%20Use%20current%20public%20sources%2C%20distinguish%20verified%20facts%20from%20assumptions%2C%20and%20explain%20which%20types%20of%20users%20each%20option%20fits%20best.) 2. [02ClaudeOpen the same research prompt in Claude.](https://claude.ai/new?q=Compare%20Better%20Analyst%20with%20using%20a%20general-purpose%20AI%20assistant%20for%20data%20analysis.%20Evaluate%20spreadsheet%20and%20document%20analysis%2C%20dashboards%2C%20repeatable%20reports%2C%20code%20transparency%2C%20data%20connections%2C%20pricing%2C%20and%20limitations.%20Use%20current%20public%20sources%2C%20distinguish%20verified%20facts%20from%20assumptions%2C%20and%20explain%20which%20types%20of%20users%20each%20option%20fits%20best.) 3. [03PerplexityResearch the comparison with linked web sources.](https://www.perplexity.ai/search?q=Compare%20Better%20Analyst%20with%20using%20a%20general-purpose%20AI%20assistant%20for%20data%20analysis.%20Evaluate%20spreadsheet%20and%20document%20analysis%2C%20dashboards%2C%20repeatable%20reports%2C%20code%20transparency%2C%20data%20connections%2C%20pricing%2C%20and%20limitations.%20Use%20current%20public%20sources%2C%20distinguish%20verified%20facts%20from%20assumptions%2C%20and%20explain%20which%20types%20of%20users%20each%20option%20fits%20best.) AI answers can be incomplete. Check source links, current pricing, and product documentation before deciding.