How to sum in Google Sheets: SUM, SUMIF and SUMIFS with examples

SUM, SUMIF and SUMIFS: syntax and a first example

All examples use one table: the ten bestselling gadgets of a store, with the type, item, price and units sold in August.

SUM function

SUM adds the numbers in one or more ranges. It ignores text and empty cells, and it updates the total whenever the data changes.

SUM function syntax

The syntax for the SUM function in Google Sheets is:

=SUM(value1, [value2, ...])

Let’s break down what these parameters represent:

• value1: The first number or range to sum.
• [value2, …]: Additional numbers or ranges to include.

You can use ranges (e.g., A1:A10) or individual numbers (e.g., 5, 10) in the formula.

SUM function example: How to sum a column in Google Sheets

To find the total units sold in column E, click an empty cell and enter:

=SUM(E3:E12)

After typing the formula, press Enter to display the result.

Here’s the breakdown:

• E3:E12: The range in column E where the sales data for the top 10 gadgets is located.

To sum the whole column, including rows added later, use an open-ended range such as =SUM(E3:E). Keep the formula itself outside column E, or it will refer to its own cell.

SUMIF function

SUMIF adds only the cells that meet one condition, such as a label, a number threshold or a date.

SUMIF function syntax

The syntax for the SUMIF function in Google Sheets is:

=SUMIF(range, criterion, [sum_range])

Here’s the breakdown:

• range: The range of cells to evaluate against the criterion.
• criterion: The condition or criteria to apply.
• [sum_range] (optional): The range of cells to sum if the criterion is met. If omitted, the function sums the cells in range itself.

SUMIF function example: How to sum a column in Google Sheets based on one condition

To total the sales of all accessories, with the word “Accessories” typed in cell G3:

=SUMIF(B3:B12, G3, E3:E12)

Here’s the breakdown:

• B3:B12: The range of cells to evaluate against the criterion.
• G3: The criteria to apply, “Accessories”
• E3:E12: The range of cells to sum.

💡 SUMIF tests a condition and adds. To test a condition and return a value instead, see our guide to the IF function.

SUMIFS function

SUMIFS adds the cells that meet every one of several conditions. Each condition has its own range, so you can test the type in one column and the price in another. Note the order: in SUMIFS the range to sum comes first, in SUMIF it comes last.

SUMIFS function syntax

The syntax for the SUMIFS function is:

=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])

Let’s break it down:

• sum_range: The range of cells to sum.
• criteria_range1: The range to apply the first criterion.
• criterion1: The condition for the first criteria range.
• [criteria_range2, criterion2, …] (optional): Additional ranges and criteria.

💡 For a chain of conditions that returns a value instead of a total, see the IFS function.

SUMIFS function example: How to sum a column in Google Sheets with multiple conditions

To total the sales of accessories priced above $200:

=SUMIFS(E3:E12, B3:B12, G3, D3:D12, ">200")

Let’s explain the formula:

• E3:E12: The range of cells containing the sales figures you want to sum.
• B3:B12: The range where the value should match the one in G3.
• G3: The specific value used to filter B3:B12.
• D3:D12: the range where the condition “>200” is applied.
• “>200”: The criterion indicating that the values in D3:D12 should be greater than $200.

With open-ended ranges such as E3:E, B3:B and D3:D, SUMIFS picks up new rows automatically. All the ranges must be the same size.

Practical examples of using SUM functions

SUM takes columns, rows, scattered cells and ranges on other tabs.

Using SUM to add cell values in a column

SUM adds only numbers and skips text and empty cells, so a header or a note inside the range does not break the total. To total the items sold in August:

=SUM(E3:E12)

The total updates whenever a value in the range changes.

Using SUM to add cell values in a row

A range can run across a row as well as down a column. With sales for June, July and August in columns E to G, the summer total for the first item is:

=SUM(E3:G3)

To get the totals for all items, copy the formula down: drag the small blue square in the bottom right corner of the cell, or double-click it.

Using SUM to add cell values in non-adjacent cells

SUM accepts several cells or ranges separated by commas, so the numbers do not have to sit next to each other. To total the summer sales of the three Apple items:

=SUM(E3, F3, G3, E5, F5, G5, E9, F9, G9)

The same total is shorter with ranges: =SUM(E3:G3, E5:G5, E9:G9). When the list of cells depends on a rule (“every Apple item”), SUMIF is the better tool.

Applying the SUM function across sheets

SUM can add cells from other tabs of the same spreadsheet. Put the tab name before the cell, followed by an exclamation mark, and wrap the name in single quotes if it contains a space. Here the July and August totals sit in cell G3 of two tabs, ‘Sales July’ and ‘Sales August’.

To calculate the total, let’s use this formula:

=SUM('Sales July'!G3, 'Sales August'!G3)

To add numbers from a different spreadsheet file, bring them in first with IMPORTRANGE.

How to quickly sum in Google Sheets without any formulas

Select the cells you want to add. Google Sheets shows the sum in the bottom right corner of the window; click it to switch to the average, minimum, maximum or count.

To write the total into the sheet without typing, select the cell below the numbers, click the Functions button (Σ) in the toolbar and choose SUM. Sheets suggests the range above; press Enter to accept it.

SUMIF and SUMIFS criteria: text, wildcards, dates and blanks

The criterion is where most of the work happens. It can be an exact text, a pattern, a comparison, a date or an empty cell.

SUMIF with exact text match criteria

A text criterion in quotes matches cells that contain exactly that text. Upper and lower case are treated as the same. To total the sales of game consoles in August:

=SUMIF(B3:B12, "Game Console", E3:E12)

SUMIF using wildcard characters for partial text match

Wildcards match part of the text. To total every item with “Apple” in its name:

=SUMIF(C3:C12, "*Apple*", E3:E12)

The asterisk * matches any number of characters, and a question mark ? matches exactly one. To match a real asterisk or question mark, put a tilde in front of it (~* or ~?).

SUMIF formulas for date-based criteria

A comparison operator in front of a date sums everything before or after it. To total the sales made before August 15th, 2024:

=SUMIF(F3:F12, "<15/08/2024", E3:E12)

A date typed as text is read according to the spreadsheet’s locale: “15/08/2024” works in a day-first locale and fails in a month-first one. "<"&DATE(2024,8,15) means the same in every locale.

For a moving window, build the criterion from TODAY(). This totals the sales of the past 20 days:

=SUMIF(F3:F12, ">" & TODAY() - 20, E3:E12)

For a period with a start and an end, use SUMIFS with two date conditions on the same column, one with “>=” and one with “<=”.

SUMIF with multiple criteria using OR logic

SUMIFS joins its conditions with AND. To sum the rows that meet either of two conditions, add two SUMIF results. To total the sales of smartphones and accessories:

=SUMIF(B3:B12, "Smartphone", E3:E12) + SUMIF(B3:B12, "Accessories", E3:E12)

This is safe when a row can meet only one of the conditions. If a row could meet both, it would be counted twice.

Using SUMIF and SUMIFS with logical operators

Comparison operators go inside the quotes, in front of the value:

• - Greater than - >
• - Less than - <
• - Equal to - =
• - Greater than or equal to - >=
• - Less than or equal to - <=
• - Not equal to - <>

To total the units sold of items priced over $500:

=SUMIF(D3:D12, ">500", E3:E12)

To decide whether expensive slow sellers are worth stocking, total the units of items priced over $500 that sold fewer than 5 units. This takes two conditions, so it is a SUMIFS:

=SUMIFS(E3:E12, D3:D12, ">500", E3:E12, "<5")

Applying SUMIF and SUMIFS with empty or non-empty cells

An empty pair of quotes “” matches blank cells, and “<>” matches cells that are not blank. Both are useful for separating complete records from incomplete ones.

Suppose some prices are missing after a price change. To total the units sold of the items with no price:

=SUMIF(D3:D12, "", E3:E12)

The opposite, the units sold of the items where both the item name and the price are filled in:

=SUMIFS(E3:E12, C3:C12, "<>", D3:D12, "<>")

Using SUMIFS to sum cells with multiple AND conditions

Every condition in a SUMIFS must be true for a row to be added. To total the Apple accessories that sold more than one unit:

=SUMIFS(E3:E12, B3:B12, "Accessories", C3:C12, "*apple*", E3:E12, ">1")

The sum range (E3:E12) is also used as a criteria range here, which is allowed.

Combining SUM functions with other Google Sheets functions

When a condition is too awkward for a SUMIF criterion, combine SUM with FILTER, ARRAYFORMULA, FIND or TODAY.

Combining SUM and FILTER functions

Combining the SUM and FILTER functions in Google Sheets allows you to sum only the values that meet specific criteria. The FILTER function narrows down the dataset to include only the rows that match your conditions, and the SUM function then calculates the total of those filtered values.

To total the August sales of items priced over $500:

=SUM(FILTER(E3:E12, D3:D12 > 500))

The result is the same as the SUMIF above. FILTER earns its place when the condition is a calculation, for example D3:D12 * E3:E12 > 5000, which a SUMIF criterion cannot express.

SUM with ARRAYFORMULA to sum multiple rows

ARRAYFORMULA lets SUM work on a calculation done row by row. To get total revenue, multiply the price of each item by the units sold and add up the results:

=ARRAYFORMULA(SUM(D3:D12 * E3:E12))

=SUMPRODUCT(D3:D12, E3:E12) returns the same number without ARRAYFORMULA.

Using SUMIF with FIND and ARRAYFORMULA for case-sensitivity

The SUMIF function in Google Sheets is not case-sensitive by default. However, you can combine SUMIF with FIND and ARRAYFORMULA to perform a case-sensitive sum.

The FIND function is case-sensitive, meaning it distinguishes between uppercase and lowercase letters. By using it within an ARRAYFORMULA, you can create a case-sensitive condition for summing values.

Suppose the item names are typed inconsistently, and you want the sales only of the rows that contain “Apple” with exactly that capitalization (not “apple” or “APPLE”).

Let’s apply the formula:

=SUMIF(ARRAYFORMULA(FIND("Apple", C3:C7)), ">0", E3:E7)

Here’s the breakdown:

• FIND(“Apple”, C3:C7): searches for the substring “Apple” in each cell within the range C3:C7. It returns the position of the substring if found and an error if not found. This function is case-sensitive.
• ARRAYFORMULA(…): ensures that FIND is applied to each cell in the range C3:C7.
• SUMIF(…, “>0”, E3:E7): sums the values in E3:E7 (Sales August) where the result of FIND is greater than 0 (meaning “Apple” was found in that exact case).

The formula returns the sales of the items whose names contain “Apple” in that exact case, and leaves out “apple” and “APPLE”.

Combining SUMIFS with TODAY function

With TODAY as a criterion, the total moves with the calendar. To total the items sold today:

=SUMIFS(E3:E7, F3:F7, TODAY())

This works when column F holds dates only. If it holds date and time, no cell equals TODAY() exactly; use two conditions instead: ">="&TODAY() and "<"&TODAY()+1.

Why a SUM, SUMIF or SUMIFS total is wrong

A formula that returns an error is the easy case. The harder one is a total that looks plausible and is wrong. These are the usual causes.

Syntax error in SUM formula

⚠️ Error: Incorrectly written SUM formula due to missing or extra parentheses, commas, or incorrect syntax.

Example of an error with a missing closing parenthesis

=SUM(E3, E5, E7

Another example with a missing comma between E5 and E7:

✅ Solution: Ensure all opening parentheses have corresponding closing parentheses. Use commas to separate individual arguments or cell ranges within the function. Confirm that the function name and arguments follow Google Sheets syntax rules.

Cells not recognized as numbers

⚠️ Error: SUM skips cells that hold text, including numbers stored as text. They look like numbers, usually sit on the left of the cell instead of the right, and are left out of the total without any warning. Imported and pasted data is the usual source.

✅ Solution: Convert the cells to real numbers:

• Set the format: Select the cells, click the “123” (More formats) button in the toolbar and choose “Number”.
• Remove what is not a number: A currency symbol, a space or a leading apostrophe keeps the cell as text. Find and replace removes them in bulk.
• Convert in the formula: =SUM(ARRAYFORMULA(VALUE(E3:E12))) turns text numbers into numbers before adding them, which helps when the source range is refreshed and cannot be edited.

Incorrect cell references in SUM function

⚠️ Error: Incorrect cell references can lead to incorrect totals when using the SUM function. This issue arises if the formula includes the wrong cells or an unintended range of cells.

✅ Solution: Double-click the cell with the formula. Google Sheets outlines the referenced ranges in color, so a range that stops one row short or covers the wrong column is visible at once. This happens most often after rows are inserted at the edge of a range.

Circular dependency detected in SUM calculation

⚠️ Error: Circular dependency in a SUM calculation occurs when the formula refers to its cell either directly or indirectly, creating a loop that prevents proper calculation.

✅ Solution: Identify and remove the circular reference by checking the formula to ensure it does not include its cell or range. You may need to adjust the formula or use different cells to avoid the loop. Google Sheets shows a #REF! error with the message “Circular dependency detected” in this case.

Formula errors in SUM range

⚠️ Error: If any cell in the range contains an error such as #N/A or #DIV/0!, SUM returns that error instead of a total. The problem is in the source cell, not in the SUM.

✅ Solution: Fix the formula in the cell that shows the error. If the error is expected (a lookup with no match, for example), wrap that formula in IFERROR so it returns 0 or an empty text, and the SUM will work again.

SUM includes hidden and filtered rows

⚠️ Error: SUM adds every cell in the range, including rows that are hidden or filtered out. The total is then larger than the sum of the rows you can see.

✅ Solution: To add only the visible rows, use SUBTOTAL instead of SUM. =SUBTOTAL(109, E3:E12) sums the range and ignores rows hidden by a filter or hidden manually. Code 9 ignores filtered rows but still includes rows hidden manually.

Mismatched range sizes in SUMIF and SUMIFS

⚠️ Error: The criteria range and the sum range do not have the same number of cells. SUMIFS returns an error; SUMIF may return a total built from the wrong rows.

✅ Solution: Make sure the range you’re applying the condition to and the range you’re summing have the same number of rows (or columns, if you’re working horizontally). Start both ranges on the same row as well: B3:B12 with E3:E12, not with E2:E11.

Incorrect criteria syntax in SUMIF

⚠️ Error: If your SUMIF formula isn’t giving the right result or shows an error, it might be due to how the criteria are written. This often happens when quotes are missing, characters are misplaced, or the format just doesn’t match what the formula expects.

✅ Solution: Ensure that the criteria are properly enclosed in quotes for text or formatted correctly for numeric comparisons. Double-check for typos and ensure that the criteria are specific and correctly structured.

Data type mismatch in SUMIF

⚠️ Error: A mismatch between the type of data in your range and the criteria you’re using can throw off your SUMIF. For example, if your range contains numbers but your criteria are treated as text, the function might not work the way you expect.

✅ Solution: Make sure text criteria are inside quotes, and that number comparisons (like “>100”) are correctly formatted. It’s also a good idea to check for typos or extra spaces that can break the formula.

Tips for writing SUMIF criteria

Most SUMIF mistakes are in the criterion. Two rules cover nearly all of them.

Put text, wildcards and operators in quotes

When the criterion includes text, a wildcard character, or a logical operator followed by a number, text, or date, enclose the criterion in quotation marks.

For example:

• =SUMIF(A2:A10, “Apple”, B2:B10)
• =SUMIF(A2:A10, “*”, B2:B10)
• =SUMIF(A2:A10, “>5”)
• =SUMIF(A5:A10, “<>Apple”, B5:B10)

Join an operator to a cell reference with &

If the criterion combines a logical operator with a cell reference or another function, put the operator in quotes and join the reference with an ampersand (&). Inside the quotes, B2 would be read as the text “B2”.

For example:

• =SUMIF(A2:A10, “>”&B2)
• =SUMIF(A2:A10, “>”&TODAY(), B2:B10)

Two more things to remember: SUMIF takes one condition only (use SUMIFS for more), and it is not case-sensitive (see the FIND example above for a case-sensitive total).

Related Google Sheets functions

Functions that are often used next to SUM, each with its own guide:

• VLOOKUP: Searches for a value in the first column of a range and returns a matching value from another column in the same row.
• UNIQUE: Pulls out only the distinct values in a range, removing any duplicates.
• Pivot Tables: Ideal for summarizing and reorganizing large datasets, making analysis and reporting more straightforward.
• MATCH: Locates and returns the position of a specific item within a range, useful for indexing.
• COUNT and COUNTA: COUNT adds up the number of cells that contain numbers, while COUNTA counts everything that’s not empty.
• INDEX and MATCH: This combo gives you a more flexible way to look up values compared to VLOOKUP, especially when your data isn’t structured in a way that VLOOKUP expects.
• AVERAGE: Calculates the mean of a set of numbers, ideal for finding central tendencies in data sets.
• RANK: Returns the position of a value within a list of numbers.
• PERCENTILE: Returns the value at a given percentile of a dataset.
• PERCENTRANK: Returns the percentage rank of a value within a dataset.

A correct total of the wrong data is still wrong

SUM, SUMIF and SUMIFS are exact. They add what is in the range, and they have no way of knowing that the range is last week’s export, that someone filtered out the refunds before pasting, or that two rows were pasted twice. When a total in a report is challenged, the question is rarely about the formula. It is about where the numbers came from.

The OWOX Data Marts extension for Google Sheets covers that half. A data analyst publishes a data mart (a table or an SQL query in your data warehouse) and makes it available for reports. In Sheets you open the extension, pick the data mart, tick the columns you want and run it. The rows land in a tab, ready for your formulas.

What changes for the person who maintains the sheet:

• Refresh replaces re-export. Refresh current report in the extension menu re-runs the same pull, and a scheduled refresh keeps the tab current without anyone touching it.
• The source is written on the sheet. The header cell of an imported tab carries a note with the data mart it came from, the time of the import and a link to the report.
• No SQL and no warehouse access needed. The analyst decides which data marts and columns are available. You choose from that list.

The extension does not write or change the analyst’s SQL, and it does not replace SUMIFS: you still decide what to add up and by which condition. You just add up rows you can trace. The extension comes with the OWOX Cloud plans of OWOX Data Marts; spreadsheet reporting describes both ways data reaches a sheet.

Google Sheets TipsPivot tables in Google Sheets: how to create and use themOctober 2, 2026→Google Sheets TipsQUERY with CONCATENATE in Google Sheets: join two columnsOctober 2, 2026→Google Sheets TipsIF with VLOOKUP in Google Sheets: formulas and examplesOctober 2, 2026→

See all articles →