How to Use SUMIF in Google Sheets

What SUMIF Does and Why You Need It

SUMIF is a function that sums values in one range based on a condition in another range. A basic SUM function adds all values in a range regardless of their properties. SUMIF adds only the values that meet a specific criterion. This lets you quickly total sales for a specific product, sum expenses in a particular category, or calculate revenue from a certain region without manually filtering or creating helper columns.

Imagine a spreadsheet with product names in column A and sales amounts in column B. You want the total sales for “Widget” products only. Without SUMIF, you would manually find every row with “Widget”, add up the numbers, or spend time filtering and copying values. With SUMIF, you type one formula: =SUMIF(A:A,”Widget”,B:B). Sheets instantly returns the sum of all sales where the product name is “Widget”.

SUMIF scales effortlessly. If your data grows from 100 rows to 10,000 rows, the formula works the same. Add a new “Widget” sale tomorrow, and the formula automatically includes it in the total. This makes SUMIF invaluable for dashboards, reports, and any sheet where you need conditional summaries that update in real time.

Syntax Explained: Range, Criterion, Sum Range

The SUMIF function has three main parts: =SUMIF(range, criterion, sum_range).

Range: The range of cells to check against your criterion. This is where the function looks for matches. It could be a column of product names, a column of dates, a column of status values, or any data that determines which rows to include in the sum.

Criterion: The condition you want to match. This could be a specific text value like “Apple”, a number like 500, a comparison like “>1000”, or a formula. Sheets evaluates each cell in the range against this criterion and marks matches.

Sum_range: The range of numbers to add up, but only for rows where the range met the criterion. If you’re looking for “Widget” in column A and want to sum column B, the range is A:A and the sum_range is B:B. These must have the same number of rows.

Here’s a concrete example. You have a sales spreadsheet with Region in column A (North, South, East, West), Sales in column B, and Quarter in column C. To sum all sales for the North region, write =SUMIF(A:A,”North”,B:B). Sheets checks every cell in column A for the text “North”. For each match, it sums the corresponding value in column B. The result is total sales from the North region.

The range and sum_range don’t have to be adjacent. You could check a criteria range in column D but sum values from column B. What matters is that they have the same length. If your range is A1:A100, your sum_range should also be 100 rows, like B1:B100.

Summing by Text Criteria

Text criteria are the simplest to use. Put your criterion in quotes. To sum all sales for product “Desk”, write =SUMIF(A:A,”Desk”,B:B). To sum all expenses labeled “Travel”, write =SUMIF(C:C,”Travel”,D:D). Text matching is case-insensitive, so “desk”, “Desk”, and “DESK” all match.

You can also place the criterion in a cell reference instead of hardcoding it. If cell F1 contains “Desk”, write =SUMIF(A:A,F1,B:B). This makes your formula dynamic. Change F1 to “Chair”, and the formula automatically recalculates for “Chair” sales. This is useful for dashboards where users select a product from a dropdown and the sum updates instantly.

Text criteria in SUMIF are exact matches. If you have “Desk” and “Desk Chair”, a criterion of “Desk” will not match “Desk Chair” unless you use wildcards. More on that in the next section.

Summing by Number Criteria

Number criteria use comparison operators: greater than (>), less than (<), equal to (=), greater than or equal to (>=), less than or equal to (<=), and not equal to (<>). Enclose the entire criterion in quotes.

To sum all orders over $500, write =SUMIF(B:B,”>500″,C:C). Sheets checks column B for values greater than 500 and sums the corresponding values in column C. To sum all expenses of exactly $50, write =SUMIF(D:D,”=50″,E:E). To sum all sales that are NOT equal to zero, write =SUMIF(B:B,”<>0″,C:C).

You can reference cells in your criterion. To sum all sales greater than the value in cell F2, write =SUMIF(B:B,”>”&F2,C:C). The ampersand (&) concatenates the operator with the cell reference. If F2 contains 1000, this becomes “>1000” and the formula sums all sales exceeding 1000.

These numeric comparisons are perfect for setting thresholds. Sum all orders above a minimum order value, all invoices overdue by more than 30 days, all scores that exceed the passing grade, or all inventory below a reorder level. One formula handles millions of rows of data.

Summing by Date Criteria

Dates work with comparison operators just like numbers. To sum all sales after January 1, 2026, write =SUMIF(A:A,”>2026-01-01″,B:B) or =SUMIF(A:A,”>”&DATE(2026,1,1),B:B). The DATE function builds a proper date value from year, month, and day.

Use TODAY() to reference the current date dynamically. To sum all invoices due by today, write =SUMIF(B:B,”<="&TODAY(),C:C). To sum all transactions from the past 30 days, write =SUMIFS (covered in the next section, but SUMIF alone can't check two date conditions). To sum all sales from this year, use YEAR() to extract the year from each date in your range and compare it to YEAR(TODAY()).

This is invaluable for revenue recognition, aging analysis, and cash flow forecasting. Finance teams can instantly see revenue earned this year or accounts receivable over 90 days old without running reports manually.

SUMIF with Wildcards: Partial Text Matching

Wildcards let you match partial text. Use asterisk (*) to match any number of characters and question mark (?) to match a single character. To sum all sales for products starting with “Widget”, write =SUMIF(A:A,”Widget*”,B:B). The asterisk acts as a wildcard meaning “any characters after Widget”. So “Widget”, “Widget Small”, “Widget Large”, and “Widget Pro” all match.

To sum expenses for all items containing “ship” anywhere in their name, write =SUMIF(C:C,”*ship*”,D:D). The asterisks on both sides match any characters before or after “ship”. This catches “Shipping”, “Reshipping”, “Airship”, and other variations.

Use the question mark to match exactly one character. To sum sales where the product code is “W1A” or “W2A” or “W9A” (anything in the middle), write =SUMIF(A:A,”W?A”,B:B). The question mark matches any single character in that position.

Wildcards are flexible but remember they match text, not numbers. “3*” will match any cell starting with 3, like “300”, “3-inch”, or “3rd Place”, but it matches them as text, not numeric values. If you need to match partial numbers, convert them to text first or use SUMIFS with multiple conditions.

SUMIF vs. SUMIFS: When You Need More Than One Condition

SUMIF handles one criterion. SUMIFS handles multiple criteria. If you want to sum sales for “Widget” products in the “North” region, SUMIF alone can’t do it. You need SUMIFS.

The syntax is =SUMIFS(sum_range, criteria_range1, criterion1, criteria_range2, criterion2, …). Notice sum_range comes first, unlike SUMIF. After that, you list pairs of criteria ranges and criteria. Each row must match all criteria to be included in the sum.

Here’s an example. Columns: Product (A), Region (B), Sales (C). To sum sales for “Widget” in “North”, write =SUMIFS(C:C,A:A,”Widget”,B:B,”North”). Sheets checks if A=”Widget” AND B=”North” for each row. Only rows matching both conditions contribute to the sum.

You can add as many conditions as you need. To sum sales for “Widget” in “North” in Q1 2026 (where quarter is in column D), write =SUMIFS(C:C,A:A,”Widget”,B:B,”North”,D:D,”Q1 2026″). Each additional condition narrows the results further.

In contrast, here is SUMIF: =SUMIF(A:A,”Widget”,C:C) sums all Widget sales regardless of region. If you need to sum by region too, you’d need separate SUMIF formulas for each region and then add them together, which is cumbersome. SUMIFS eliminates this repetition.

SUMIF Across Multiple Sheets

You can use SUMIF to sum data from different sheets in the same workbook. If you have quarterly sales sheets (Q1, Q2, Q3, Q4) and you want to sum annual sales, reference each sheet in separate SUMIF formulas and add them together.

The syntax is =SUMIF(SheetName!Range, Criterion, SheetName!SumRange). For example, =SUMIF(Q1!A:A,”North”,Q1!B:B) sums all North region sales from the Q1 sheet. To combine quarters, write =SUMIF(Q1!A:A,”North”,Q1!B:B)+SUMIF(Q2!A:A,”North”,Q2!B:B)+SUMIF(Q3!A:A,”North”,Q3!B:B)+SUMIF(Q4!A:A,”North”,Q4!B:B).

This is cleaner than copying data across sheets. Each quarter owns its own sheet, and a summary sheet pulls data from all four using SUMIF. Update Q1 data, and the annual summary recalculates automatically. For many sheets, consider using an array formula instead of adding individual SUMIF formulas, but the manual approach works fine for 4 or 5 sheets.

Practical Use Cases

Department Budget Rollup: Columns for Account, Department, and Amount. Each row represents an account belonging to a department. Use =SUMIF(B:B,”Sales”,C:C) to sum all accounts in the Sales department. Create a summary table with one formula for each department. Finance managers see total spending by department without manually filtering or using pivot tables.

Regional Sales Total: Columns for Date, Region, Product, and Sales Amount. Use =SUMIF(B:B,”East”,D:D) to sum all sales in the East region. Build a dashboard with one SUMIF formula for each region. When new sales are added, the regional totals update automatically. Sales managers see regional performance at a glance.

Filtering Spend by Category: Columns for Date, Category, and Amount. Use =SUMIF(B:B,”Office Supplies”,C:C) to sum all spending on office supplies. Add more formulas for other categories: Equipment, Travel, Meals, etc. An expense dashboard shows where money is going. Adjust budget allocations based on actual spending by category.

Summing Completed Tasks: Columns for Task, Owner, and Status. Use =SUMIF(C:C,”Completed”,D:D) if column D contains hours or points for each task. Sum all hours worked on completed tasks. Use =SUMIF(C:C,”Completed”) (without sum_range) to count the number of completed tasks. Project managers see productivity metrics and task completion rates without manually tallying.

Common Errors and Fixes

#VALUE! Error: This usually means your criterion or range contains invalid data types. If you’re comparing numbers but the cell contains text, or vice versa, SUMIF may throw an error. Check that your range contains the data type you expect (numbers for numeric criteria, text for text criteria). Also verify that sum_range contains only numbers, not mixed text and numbers.

Range Size Mismatch: SUMIF requires range and sum_range to have the same number of rows. If range is A1:A50 but sum_range is B1:B100, Sheets may give unexpected results or an error. Count the rows in both ranges and make sure they match. Using entire column references like A:A and B:B avoids this issue because Sheets automatically matches the lengths.

Criterion Not Matching Because of Extra Spaces: If your criterion is “North” but cells contain “North ” (with trailing space), they won’t match. Use TRIM() in your data entry or in your formula. Rewrite as =SUMIF(TRIM(A:A),”North”,B:B), though TRIM doesn’t always work in array formulas depending on your Sheets version. Better to clean the data first by selecting the range and using Find & Replace to remove extra spaces.

Criterion Not Matching Because of Different Formatting: If column A contains dates formatted as “1/15/2026” but your criterion is “2026-01-15”, they won’t match as text. Use DATE() function to create a proper date criterion or convert all dates to the same format first. Check if you’re mixing text dates with actual date values.

Formula Returns Zero Instead of an Error: If your SUMIF returns 0, it might mean no rows met your criterion, which is correct. Double-check that your criterion exactly matches the data. Use a simple COUNTIF to verify how many cells match before using SUMIF. Write =COUNTIF(A:A,”North”) to count matches. If this returns 0, your criterion doesn’t match the data.

FAQ Schema

SUMIF is a foundational function for anyone working with data in Google Sheets. It replaces hours of manual filtering and copying with a single formula. Whether you’re summarizing sales by region, expense by category, or task hours by status, SUMIF delivers the answer in milliseconds. Once you master this function, you’ll find yourself using it in nearly every spreadsheet. Combine it with SUMIFS to handle multiple conditions, and you have the power to build dynamic dashboards and reports that update automatically as your data changes.

For related functions, explore how COUNTIF in Google Sheets counts cells matching a criterion instead of summing them. You might also want to learn about IF formula in Google Sheets to add conditional logic within cells. Understanding VLOOKUP in Google Sheets helps you look up values from other tables and combine them with SUMIF for advanced data analysis. For visual analysis of your summed data, see how to make a graph in Google Sheets to create charts from your conditional summaries. When your data includes dates, learn to sort by date in Google Sheets before applying SUMIF rules. For cleaner data entry, explore drop-down list in Google Sheets to standardize the values SUMIF will check. Finally, remove duplicates in Google Sheets before running SUMIF to ensure accurate totals.