SumIf

SumIf adds up the values in a column based on a condition.

Syntax: SumIf(column, condition).

Example: in the table below, SumIf([Payment], [Plan] = "Basic") would return 200.

Aggregation formulas like sumif should be added to the query builder’s Summarize menu > Custom Expression (scroll down in the menu if needed).

Parameters

• column can be the name of a numeric column, or an expression that returns a numeric column.
• condition is an expression that returns a boolean value (true or false), like the expression [Payment] > 100.

Multiple conditions

To sum a column based on multiple mandatory conditions, combine the conditions using the AND operator:

SumIf([Payment], ([Plan] = "Basic" AND month([Date Received]) = 10))

This expression would return 200 on the sample data above, as it sums all of the payments received for Basic Plans in October.

To sum a column with multiple optional conditions, combine the conditions using the OR operator:

SumIf([Payment], ([Plan] = "Basic" OR [Plan] = "Business"))

Returns 600 on the sample data.

To combine mandatory and optional conditions, group the conditions using parentheses:

SumIf([Payment], ([Plan] = "Basic" OR [Plan] = "Business") AND month([Date Received]) = 10)

Returns 400 on the sample data.

Tip: make it a habit to put parentheses around your AND and OR groups to avoid making mandatory conditions optional (or vice versa).

Conditional subtotals by group

To get a conditional subtotal for a category or group, such as the total payments per plan, you’ll:

1. Write a sumif formula with your conditions.
2. Add a Group by column in the query builder.

To sum payments for the Business and Premium plans:

SumIf([Payment], [Plan] = "Business" OR [Plan] = "Premium")

Or, sum payments for all plans that aren’t “Basic”:

SumIf([Payment], [Plan] != "Basic") 

The “not equal” operator != should be written as “!=”.

To view those payments by month, set the Group by column to “Date Received: Month”.

Tip: when sharing your work with other people, it’s helpful to use the OR filter, even though the != filter is shorter. The inclusive OR filter makes it easier to understand which categories (e.g., plans) are included in the sum.

Accepted data types

Related functions

Metabase

• case
• CumulativeSum

Other tools

• SQL
• Spreadsheets
• Python

case

You can combine the Sum and case formulas

Sum(case([Plan] = "Basic", [Payment]))

to do the same thing as the SumIf formula:

SumIf([Payment], [Plan] = "Basic")

The case version lets you sum a different column when the condition isn’t met. For example, you could create a column called “Revenue” that:

• sums the “Payments” column when “Plan = Basic”, and
• sums the “Contract” column otherwise.

sum(case([Plan] = "Basic", [Payment], [Contract]))

CumulativeSum

SumIf doesn’t do running totals. You’ll need to combine the CumulativeSum aggregation with the case formula.

For example, to get the running total of payments for the Business and Premium plans by month (using our payment sample data):

Create an aggregation from Summarize > Custom expression:

CumulativeSum(case(([Plan] = "Basic" OR [Plan] = "Premium"), [Payment], 0))

Don’t forget to set the Group by column to “Date Received: Month”.

SQL

When you run a question using the query builder, Metabase will convert your graphical query settings (filters, summaries, etc.) into a query, and run that query against your database to get your results.

If our payment sample data is stored in a PostgreSQL database:

SELECT 
    SUM(CASE WHEN plan = "Basic" THEN payment ELSE 0 END) AS total_payments_basic
FROM invoices

is equivalent to the Metabase SumIf expression:

SumIf([Payment], [Plan] = "Basic")

To add multiple conditions with a grouping column:

SELECT 
    DATE_TRUNC("month", date_received)                       AS date_received_month,
    SUM(CASE WHEN plan = "Business" THEN payment ELSE 0 END) AS total_payments_business_or_premium
FROM invoices
GROUP BY 
    DATE_TRUNC("month", date_received)

The SQL SELECT statement matches the Metabase SumIf expression:

SumIf([Payment], [Plan] = "Business" OR [Plan] = "Premium")

The SQL GROUP BY statement maps to a Metabase Group by column set to “Date Received: Month”.

Spreadsheets

If our payment sample data is in a spreadsheet where “Payment” is in column A and “Date Received” is in column B:

=SUMIF(B:B, "Basic", A:A)

produces the same result as:

SumIf([Payment], [Plan] = "Basic")

To add additional conditions, you’ll need to switch to a spreadsheet array formula.

Python

If our payment sample data is in a pandas dataframe column called df:

df.loc[df['Plan'] == "Basic", 'Payment'].sum()

is equivalent to

SumIf([Payment], [Plan] = "Basic")

To add multiple conditions with a grouping column:

import datetime as dt

## Optional: convert the column to a datetime object

    df['Date Received'] = pd.to_datetime(df['Date Received'])

## Extract the month and year

    df['Date Received: Month'] = df['Date Received'].dt.to_period('M')

## Add your conditions

    df_filtered = df[(df['Plan'] == 'Business') | (df['Plan'] == 'Premium')]

## Sum and group by

    df_filtered.groupby('Date Received: Month')['Payment'].sum()

These steps will produce the same result as the Metabase SumIf expression (with the Group by column set to “Date Received: Month”).

SumIf([Payment], [Plan] = "Business" OR [Plan] = "Premium")

Further reading

• Custom expressions documentation
• Custom expressions tutorial