How do you replicate Excel SUMIFs in Tableau with calculated fields?

Tableau FAQs
December 10, 2018

Get Started Transforming Your Data in Snowflake

This post walks through how to replicate the Excel SUMIFs function in Tableau. To do the same Excel function in Tableau, you need to use two functions. These are SUM and IF conditions.

First, we need to indicate what will eventually need to be done with the result; we first specify the SUM function and open the expression brackets. Next, you need to designate logical actions with data (in our example, two logical expressions), the result of which will be TRUE or FALSE. These actions must show TRUE in both cases; only in this case, we can calculate the amount according to the condition. But if these conditions are not met, we need to specify an alternative execution option for the logical expression. In the end, the result of the SUM IF expression will be either the sum of SalesLastYear or 0.

SUM( 

    IF [TerritoryName] = "Central"

    OR [TerritoryName] = "Canada" 

    THEN [SalesLastYear] 

    ELSE 0 END)

SUM( – Here, we mean that the result of executing the function will be the sum of the result.

IF [TerritoryName] = “Central” – This is the beginning of the condition, and in this line, we indicate [field name], which should be equal to “Central”

OR [TerritoryName] = “Canada” – Next, we apply the OR condition, in which we also indicate [Field name], which should equal “Canada”

THEN [SalesLastYear] – Since the previous two conditions check the coincidence of the selected field in the condition and return TRUE or FALSE as a result, we need to indicate what should happen if both of these conditions are met(both = TRUE). To do this, we specify the field in the INT or DECIMAL format (since the SUM function only works with numeric formats).

ELSE 0 END) – In the end, we need to indicate what should happen if the previous two conditions fail. To do this, we specify 0 and close the function with END and close the bracket.