How to Use the 8 Most Common Smartsheet Formulas (Step-by-Step Guide)

⬇ Save as PDF

1. How do I do a VLOOKUP in Smartsheet?

What it does: Searches for a value in the first column of a range (like a lookup table) and returns a value from another column in the same row.

When & why to use it: Use VLOOKUP when you have two tables and a shared unique identifier. It’s great for pulling a client’s info into a project sheet by matching on Client ID. However, the lookup value must be in the first column of your range.

How to use VLOOKUP (Step-by-Step):

1. Identify the lookup value: (e.g., [Product ID]@row).
2. Set up your reference table: Make sure the first column contains the lookup values. Create a cross-sheet reference if needed.
3. Write the formula: =VLOOKUP(lookup_value, search_range, column_index, false)
   • lookup_value: The value you are searching for.
   • search_range: The range containing the key (col 1) and value to return.
   • column_index: The number of the column to return (e.g., 4 for the 4th column).
   • false: Tells the formula to return only exact matches.

⬇ Save as PDF

2. How do I use INDEX and MATCH in Smartsheet?

What it does: INDEX/MATCH is a flexible alternative to VLOOKUP. MATCH finds the row number of a value, and INDEX returns the value from that row in a specific column. It’s like saying: "Find which row contains X, then give me the value from that same row in Y column."

Why use it? It can look to the left (VLOOKUP cannot), it’s faster on large sheets, and it doesn't break if you move columns around.

How to use INDEX/MATCH (Step-by-Step):

1. Set up references: Create a reference for the column to return ({Return Range}) and a reference for the column to search ({Lookup Range}).
2. Write the formula: =INDEX({Return Range}, MATCH(lookup_value, {Lookup Range}, 0)).
3. MATCH finds the row: MATCH([ID]@row, {ID Col}, 0) returns the row number.
4. INDEX gets the value: INDEX({Name Col}, row_number) retrieves the data.

Example: Retrieve Client Name based on Client ID.

⬇ Save as PDF

3. How do I use INDEX and COLLECT in Smartsheet?

What it does: The superpower of Smartsheet formulas. It acts like a "VLOOKUP with multiple criteria". COLLECT filters a range based on conditions, and INDEX returns the first result from that filtered list.

When to use it: When you need to match several conditions at once. E.g., Find the Price of a Product where Region = "West" AND Type = "Software".

How to use INDEX/COLLECT (Step-by-Step):

1. Identify the Return Range: The column containing the value you want.
2. Identify Criteria: The conditions that must be met (e.g., Region="West").
3. Write the COLLECT: COLLECT(ReturnRange, CriteriaRange1, "Criterion1", ...).
4. Wrap with INDEX: =INDEX(COLLECT(...), 1). The 1 returns the first match found.

Breakdown:

• {Price}: The values to return.
• {Region}, "West": Only include rows where Region is West.
• {Type}, "Software": AND where Type is Software.
• 1: Return the 1st match.

⬇ Save as PDF

4. How do I use COUNTIFS or SUMIFS in Smartsheet?

What it does: COUNTIFS counts rows that match criteria. SUMIFS sums a range for matched rows. They are the backbone of Smartsheet metrics. Syntax: (Range1, "Criterion1", Range2, "Criterion2"...).

When & why to use it: Use for conditional math. Example: "Count tasks assigned to Ward" or "Sum Budget for Q1 Engineering Projects". They handle multiple criteria (AND logic) natively.

Visual Concept: Imagine a grid where rows that don't match Criteria 1 fade away. Then rows that don't match Criteria 2 fade away. The survivors are counted/summed.

How to use it (Step-by-Step)

1. Decide: Are you counting rows (COUNTIFS) or adding up numbers (SUMIFS)?
2. Choose Criteria: E.g., Status="Complete" AND Priority="High".
3. Write Formula:
   • COUNTIFS: =COUNTIFS(Status:Status, "Complete", Priority:Priority, "High")
   • SUMIFS: =SUMIFS(Cost:Cost, Status:Status, "Complete") — *Note: Sum Range comes FIRST.*

Scans Status for "Complete" AND Priority for "High". Counts only if both are true on the same row.

Common Mistakes

⬇ Save as PDF

5. How do I find a MAX or MIN value with criteria in Smartsheet?

What it does: Replicates Excel's MAXIFS. Finds the highest or lowest number/date among a specific subset of data.

Visual Concept: Imagine a sieve filtering rows by "Project Alpha". The numbers float up into a bubble, and the highest one pops out.

Use case: "What is the most recently completed % for Project Alpha?"

How to use it: Use COLLECT to gather a list of values that meet your criteria, then wrap it in MAX().

⬇ Save as PDF

6. How do I calculate rolling 7/30/90-day metrics in Smartsheet?

What it does: Creates dynamic reports that clearly show "Last 7 Days" or "Next 30 Days" without manual updates.

The Secret: Use TODAY(). It updates automatically when the sheet is opened or saved.

Rolling 7-Day Logic:

You need two criteria: Date is on/before today AND Date is strictly after 7 days ago.

For a 30-day window, change -7 to -30. To include the day exactly 30 days ago, use >= instead of >.

⬇ Save as PDF

7. How do I build dashboard metrics with formulas in Smartsheet?

What it means: Dashboard metrics are summary numbers or statistics displayed on a Smartsheet Dashboard. Since dashboards can’t directly calculate data, you build these metrics in sheets (often in a dedicated “Metrics Sheet” or in Sheet Summary fields) using formulas, then display them on the dashboard via widgets.

When & why to use it: Use formula-driven metrics for KPIs like "Total Projects", "Tasks Completed This Week", or "% Budget Used". A Metric Widget can only display values from a sheet cell or Sheet Summary field—it cannot calculate from a report on the fly. By building a structured metrics sheet, you ensure your dashboard stays accurate and fast.

Visual Concept: Values from various source sheets flow into a central "Metrics Sheet", are aggregated into a single KPI, and then pushed to the Dashboard widget.

How to set it up (Step-by-Step)

1. Choose a location: Create a separate "Metrics Sheet" or use "Sheet Summary" fields on your project sheet.
2. Decide your metrics: List them out (e.g., "# Open Tasks", "% Complete").
3. Write the formulas:
   • Sheet Summary: =COUNTIFS(Status:Status, "Open")
   • Metrics Sheet: =COUNTIFS({Project Status Range}, "Open") (using Cross-Sheet References).
4. Link to Dashboard: Add a "Metric Widget" on your dashboard and point it to the cell or summary field containing your calculation.

Example Scenario: You want to show "Percent Complete" on a dashboard.

Place this in a Sheet Summary field formatted as %, then link your widget to it.

Best Practices

• Organize: Use clear labels in your metrics sheet. Use blank rows or formatting to separate sections.
• Portfolio Metrics: For multi-project rollups, have each project calculate its own summary, then cell-link those summaries to a Master Metrics Sheet for a final sum.
• Mind the Limits: Smartsheet has a ~100k inbound cell link limit. For massive data sets, consider using Smartsheet Pivot or DataMesh.

⬇ Save as PDF

8. How do I concatenate or join text in Smartsheet?

What it does: Concatenation stitches text from different cells into one string. Use + for simple pairs, or JOIN for lists range.

When & why to use it: Creating unique IDs (e.g., "Order-123"), combining names ("John Doe"), or summarizing lists ("Apple, Banana, Orange").

Visual Concept: Separate pieces of text (cards) are stitched together with a "thread" (separator) to form a single, unified string.

How to use it (Step-by-Step)

1. Identify Values: Which cells do you want to combine? (e.g. [First Name]@row).
2. Choose Separator: Do you need a space, comma, or dash? Put it in quotes: " - ".
3. Write Formula:
   • Basic (+): =[First Name]@row + " " + [Last Name]@row
   • List (JOIN): =JOIN([Task]:[Task], ", ")

Example Scenario: Create a unique ID from a Product Code and Year.

Advanced: Use JOIN + COLLECT to list projects matching a status.