Smartsheet SUMIF Examples for Everyday Reports

In Smartsheet, SUMIF adds values in a sum range only when corresponding cells meet one condition. For example, with Region and Revenue columns, use =SUMIF([Region]:[Region], "West", [Revenue]:[Revenue]) to total revenue from West rows.

SUMIF is useful for quick category totals, status-based rollups, and threshold calculations without manually filtering and adding rows. The examples below use Smartsheet’s column-reference syntax; the same formulas are not written exactly like formulas for an Excel worksheet.

How SUMIF works in Smartsheet

The function takes a range to check, a criterion to apply to that range, and—optionally—a range of numbers to add. Its general structure is =SUMIF(range, criterion, [sum_range]). The square brackets in the syntax description indicate an optional argument; they are not typed around the argument as a whole.

In a Smartsheet formula, a reference such as [Region]:[Region] means the Region column, while [Revenue]:[Revenue] means the Revenue column. SUMIF checks each row in the first column and, for each match, adds the value from the corresponding row of the sum column. The ranges should cover corresponding rows in the same sheet or corresponding referenced ranges.

Range: the cells or column to evaluate.
Criterion: the text, value, cell reference, or comparison condition to match.
Sum range: the numbers to add when a row matches. If omitted, SUMIF adds matching values from the first range itself.

Example: total a category or region

Suppose a sheet has Region and Revenue columns with five records: West $1,200, East $900, West $750, West $500, and East $650. To total West revenue, enter this formula in a suitable summary cell:

=SUMIF([Region]:[Region], "West", [Revenue]:[Revenue])

The result is $2,450: $1,200 + $750 + $500. The formula tests Region but adds Revenue, so it does not add the text values in the Region column. Replace “West” with another exact region name to calculate a different total.

This pattern works for many categorical summaries: total expenses by department, hours by project, units by product, or donations by campaign. Match the criterion to the values actually stored in the column. For instance, “In progress” and “In Progress” are not necessarily interchangeable in a workflow; consistent labels make formulas easier to maintain.

Example: use a cell as the criterion

Hard-coding a condition is convenient for a one-off total, but a cell reference makes a small report easier to reuse. Suppose row 1 of a column named Selected Region contains the region to report, while Region and Revenue hold the data. Use:

=SUMIF([Region]:[Region], [Selected Region]1, [Revenue]:[Revenue])

If the value in Selected Region row 1 is West, the result is $2,450 for the sample above. Change that cell to East and the result becomes $1,550 ($900 + $650), without editing the formula. This is helpful when a dashboard or report needs a user-selectable category. Keep the input cell’s spelling and spacing consistent with the source values.

A criterion can also be a number or a comparison condition. For example, assume an Hours column contains 6, 9, 10, and 7, and the corresponding Billable Amount values are $300, $450, $500, and $350. To add amounts for rows with more than eight hours, use:

=SUMIF([Hours]:[Hours], >8, [Billable Amount]:[Billable Amount])

The result is $950, from the rows with 9 and 10 hours ($450 + $500). A comparison checks the values in the criteria range; it does not compare the sum-range values unless that is the range you supply as the first argument.

Example: total by status

For a project tracker, you may want to total budget assigned to approved work. If Status contains workflow labels and Budget contains numeric amounts, use:

=SUMIF([Status]:[Status], "Approved", [Budget]:[Budget])

For example, if the two approved rows have budgets of $1,250 and $800, the result is $2,050. Rows marked Draft, Pending, or Rejected are not included. A status-based formula is a calculation, not a filter: it leaves the sheet’s displayed rows unchanged, which is useful when the report should show a total while retaining the full task list.

Using SUMIF with another sheet

Smartsheet can use cross-sheet references in formulas. Instead of putting a full source column reference directly in the formula, create named references to the source ranges; Smartsheet represents those references in braces. For example, if you create references named Orders Region and Orders Revenue, a West total can be written as:

=SUMIF({Orders Region}, "West", {Orders Revenue})

When building the formula, use Smartsheet’s Reference Another Sheet option to select the source range and create or choose a reference. The range names inside braces must match the references available to that formula. If you rename or replace a reference, check formulas that depend on it. Cross-sheet formulas are useful for a summary sheet, but they still need compatible ranges and consistent category labels in the source data.

Do not type ordinary sheet column names in place of a cross-sheet reference and assume Smartsheet will find another sheet automatically. Establish the reference through the formula workflow, then use the resulting named range. If a cross-sheet result is unexpectedly blank or zero, verify the reference source, the range selected, and whether the criterion exactly matches the source values.

When SUMIFS is a better fit

SUMIF applies one condition. If a total must satisfy two or more conditions—such as Region equals West and Status equals Approved—use SUMIFS. In Smartsheet, SUMIFS puts the sum range first, followed by pairs of criteria ranges and criteria:

=SUMIFS([Amount]:[Amount], [Region]:[Region], "West", [Status]:[Status], "Approved")

This adds Amount only for rows that meet both conditions. For example, with two matching rows of $700 and $450, the result is $1,150. The distinction in argument order matters: SUMIF starts with the range to test, while SUMIFS starts with the range to sum. If you only have one condition, SUMIF is usually simpler to read. If you find yourself combining several separate SUMIF formulas to represent conditions that must all be true, consider whether one SUMIFS formula expresses the logic more clearly.

Common SUMIF problems and fixes

A result of zero does not always mean the formula is broken. It can mean that no criterion values matched, that the matched sum cells are empty, or that the sum values are stored as text rather than numbers. Check a few source rows directly before changing the formula.

Check column names and references. Smartsheet column references use the column name in brackets and a colon between the beginning and ending column reference. Ensure the names are spelled as they appear in the sheet.
Check the criterion. Look for extra spaces, inconsistent status labels, or a criterion cell that is empty or points to the wrong row.
Check the sum range. It should contain the numeric values you intend to total and correspond row by row with the criteria range.
Check number storage. A value that looks like a number but is stored as text may not behave like a numeric amount. Review the source cell values and column setup.
Check the condition. For comparisons, confirm the operator and threshold describe the intended boundary. “Greater than 8” excludes 8; it is not the same as “8 or more.”

Also distinguish a formula error from an unexpected but valid total. SUMIF does not change the source data, infer a business rule, or validate that labels are used consistently. If a report needs separate totals for several statuses, create separate formulas or use a report design suited to the summary rather than expecting one SUMIF to produce a whole breakdown.

Frequently Asked Questions

What is the SUMIF formula in Smartsheet?
Its structure is =SUMIF(range, criterion, [sum_range]). It checks one range against one condition and adds values from the optional sum range for matching rows.

Can Smartsheet SUMIF add values based on text?
Yes. Put the text criterion in straight double quotation marks, such as "West", and provide the numeric column as the sum range. The criterion must match the values in the selected range.

Can I use more than one condition in Smartsheet SUMIF?
SUMIF handles one condition. Use SUMIFS when all of several conditions must be true, supplying the sum range first and then each criteria-range-and-criterion pair.

Why does my SUMIF formula return zero?
Check for a mismatch in the criterion, a wrong column or cross-sheet range, blank matching sum cells, or values stored as text. Also confirm that your criteria and sum ranges line up row by row.

Can SUMIF reference another Smartsheet?
Yes. Create cross-sheet references to the needed ranges and use their brace-delimited names in the formula. The references must point to the intended source columns and contain corresponding rows.

Does SUMIF filter the rows in my sheet?
No. It calculates a total based on a condition but does not hide or remove nonmatching rows. Use a filter or report when you need to display only records that meet a condition.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.