How to Do SUMIF in Tableau

If you're coming from the world of spreadsheets, SUMIF is likely one of your most-used formulas. It's the go-to function for adding up numbers that meet a specific criterion. When you move into Tableau, you’ll quickly discover there’s no direct SUMIF function, but don’t worry - Tableau provides a much more powerful and flexible way to achieve the same result. This tutorial will walk you through how to perform conditional sums in Tableau using calculated fields.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Rethinking SUMIF for Tableau

In a spreadsheet, SUMIF works by looking at a range of cells, checking for a condition in that range, and then summing a corresponding range of numbers. A typical use case would be, "Sum all sales from the 'East' region."

Tableau thinks about data a bit differently. Instead of working with cell ranges, it operates on a row-by-row basis across your entire dataset. It then aggregates that row-level data into the visualizations you build. Because of this, the logic for a conditional sum is slightly different but more intuitive once you get the hang of it.

The core concept is this: instead of telling Tableau where to look for the condition, you tell it what to sum for each row that meets your condition. The main tool for this is combining the IF and SUM functions within a calculated field.

The Basic Tableau SUMIF Formula

The standard way to replicate a SUMIF function in Tableau is with a simple formula that tells Tableau: "For every row in my data, check if a condition is true. If it is, give me the number I want to sum. If it's not, give me nothing. Finally, add up all the numbers you collected."

Here’s the basic syntax:

SUM(IF [Dimension] = "Criteria" THEN [Measure to Sum] END)

Let's break down each part:

• SUM(...): This is the aggregation function. It tells Tableau to add up all the values that are produced by the logic inside the parentheses.
• IF...THEN...END: This is the conditional logic that gets evaluated for every single row in your data source.
• [Dimension] = "Criteria": This is your condition. For example, [Region] = "East" or [Product Category] = "Technology". You're checking if the value in a specific dimension field meets your criteria.
• [Measure to Sum]: This is the numeric field you want to add up when the condition is met, like [Sales] or [Quantity].

Notice that there is no "ELSE" part in this IF statement. When the condition [Region] = "East" is false for a given row, the IF statement returns a NULL value. The SUM() function simply ignores NULLs, which is exactly why this works perfectly. It only sums the values where the condition is true.

Step-by-Step Guide: Creating Your First Conditional Sum

Let's walk through a practical example using Tableau's sample "Superstore" dataset. Our goal is to calculate the total sales just for the "Technology" product category.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 1: Open the Calculated Field Editor

First, connect to your data source. In the Data pane on the left side of your worksheet, you'll see your dimensions and measures. To create a new calculation, you can either:

• Click the small down-arrow at the very top of the Data pane and select "Create Calculated Field."
• Right-click on any empty space within the Data pane and select "Create Calculated Field."

This will open a new window where you can write your formula.

Step 2: Name Your Calculation

Give your new field a descriptive name. This is what will appear in your Data pane, so make it clear. We'll call ours "Technology Sales." Descriptive names are essential for keeping your workbooks organized, especially a few months down the line when you need to remember what a calculation does.

Step 3: Write the Formula

In the main formula box, type the following expression:

SUM(IF [Category] = "Technology" THEN [Sales] END)

Tableau often auto-suggests field names as you type. If your formula is valid, you'll see a green checkmark and the message "The calculation is valid." at the bottom of the editor window. If you have an error, Tableau will tell you what's wrong, which is helpful for debugging.

Step 4: Use Your New Measure

Click "OK." Your new calculated field, "Technology Sales," will now appear in the Measures section of your Data pane. It behaves just like any other measure.

To use it, simply drag "Technology Sales" onto your view. For example, you can drag it onto the "Text" card in the Marks pane to see the total number. You can also compare it to your total sales by dragging the original "Sales" measure onto the view as well.

Now, try dragging the [Sub-Category] dimension to the Rows shelf. The "Technology Sales" measure will be automatically calculated for each sub-category, but it will only show values for those within the Technology category (Phones, Accessories, Copiers, and Machines). All others will be blank (or zero, depending on your formatting), because our formula effectively weeds them out.

Handling Multiple Conditions: The Tableau SUMIFS

Spreadsheets also have a SUMIFS function for scenarios where you need to meet multiple criteria. To do this in Tableau, you simply add more conditions to your IF statement using the AND or OR operators.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Using AND for Multiple Criteria

Let's say you want to calculate sales for "Technology" products that were sold specifically in the "West" region. You would add an AND operator to your condition.

The formula would be:

SUM(IF [Category] = "Technology" AND [Region] = "West" THEN [Sales] END)

This tells Tableau to only sum the [Sales] value for rows where both conditions are true simultaneously.

Using OR for "Either/Or" Criteria

What if you want to sum sales for two different categories, like "Technology" or "Office Supplies"? For that, you use the OR operator.

The formula looks like this:

SUM(IF [Category] = "Technology" OR [Category] = "Office Supplies" THEN [Sales] END)

This will sum the [Sales] for any row where the category is one of the two specified options. For checking a field against multiple values, you can also use the IN operator to make the formula cleaner:

SUM(IF [Category] IN ("Technology", "Office Supplies") THEN [Sales] END)

This does the exact same thing as the OR formula but is often easier to read and manage if you have a longer list of criteria.

A More Advanced Approach: Level of Detail (LOD) Expressions

Sometimes you need to perform a conditional sum that isn't affected by the other dimensions in your view. For example, you might want to calculate the total sales for the "Technology" category and then use that single value to compute what percentage each sub-category contributes to it.

If you use the simple SUM(IF...) method, the calculation will change based on what you have in the view (like [Sub-Category]). This is where Level of Detail (LOD) expressions come in. They allow you to compute an aggregation at a level that is independent of your visualization's structure.

The {FIXED} LOD expression is perfect for this. The syntax locks the calculation at a specific level of detail (or the entire dataset if you don't specify any dimensions).

To get the total sales for the Technology category across the entire dataset, the formula is:

{ FIXED : SUM(IF [Category] = "Technology" THEN [Sales] END) }

Here's the breakdown:

• { ... }: The curly braces signal that this is an LOD expression.
• FIXED: This keyword tells Tableau to compute the aggregation at the exact level of detail specified.
• :: Since there are no dimensions listed before the colon, the calculation is fixed to the entire dataset. It computes the one, single value for technology sales and makes that value available for every row.

When you use this new LOD calculation in a view with [Sub-Category] on the Rows shelf, you will see the same total Technology sales value repeated for every single row. This enables powerful secondary calculations, like finding the percent of total for each sub-category:

Calculation for % of Tech Sales:

SUM([Sales]) / MAX({ FIXED : SUM(IF [Category] = "Technology" THEN [Sales] END) })

This allows you to make comparisons that would be much more difficult without LODs.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Common Mistakes to Avoid

As you get comfortable with conditional sums, you might run into a few common validation hurdles. Here are a couple of things to watch out for.

1. Forgetting the SUM() Aggregator

A frequent error is writing the IF statement by itself: IF [Category] = "Technology" THEN [Sales] END. Without the SUM() wrapper, this calculation is performed at the row level. If you try to mix it in a view with other aggregated measures (which is almost always the case), Tableau will return an error complaining you can't mix aggregate and non-aggregate comparisons.

2. Placing the IF Condition Outside the SUM

Another common mistake is reversing the logic: IF [Category] = "Technology" THEN SUM([Sales]) END. This also causes a "cannot mix aggregate and non-aggregate" error. The [Category] field is a non-aggregated, row-level dimension, while SUM([Sales]) is an aggregated measure. The two can't be combined in this way. Always keep the IF statement inside the aggregation.

Final Thoughts

While Tableau doesn't have a direct SUMIF function, it offers a more powerful solution through calculated fields. By embedding an IF statement inside a SUM aggregation, you gain precise control over your data, allowing for everything from simple conditional totals to complex, multi-layered analyses with Level of Detail expressions.

Sometimes, even with the right tools, you just need a quick answer without pausing to write the perfect formula or build out a new visualization from scratch. This is where we built Graphed to simplify the entire process. Instead of creating calculated fields, you can connect your data sources and simply ask for what you need in plain English, like "What were our total sales for technology products in the west region last quarter?" We instantly analyze the data and generate the charts and dashboards for you, letting you get insights in seconds, not hours.