CountIf

CountIf counts the total number of rows in a table that match a condition. CountIf counts every row, not just unique rows.

Syntax: CountIf(condition).

Example: in the table below, CountIf([Plan] = "Basic") would return 3.

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

CountIf accepts a function or conditional statement that returns a boolean value (true or false).

We’ll use the following sample data to show you CountIf with required, optional, and mixed conditions.

To count the total number of rows in a table that match multiple required conditions, combine the conditions using the AND operator:

CountIf(([Plan] = "Basic" AND [Active Subscription] = true))
    
                
Copy
    
    
                
Copied
    

This expression will return 2 on the sample data above (the total number of Basic plans that have an active subscription).

To count the total rows in a table that match multiple optional conditions, combine the conditions using the OR operator:

CountIf(([Plan] = "Basic" OR [Active Subscription] = true))
    
                
Copy
    
    
                
Copied
    

Returns 4 on the sample data: there are three Basic plans, plus one Premium plan has an active subscription.

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

CountIf(([Plan] = "Basic" OR [Plan] = "Business") AND [Active Subscription] = "false")
    
                
Copy
    
    
                
Copied
    

Returns 2 on the sample data: there are only two Basic or Business plans that lack an active subscription.

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

In general, to get a conditional count for a category or group, such as the number of inactive subscriptions per plan, you’ll:

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

Using the sample data:

Count the total number of inactive subscriptions per plan:

CountIf([Active Subscription] = false)
    
                
Copy
    
    
                
Copied
    

Alternatively, if your Active Subscription column contains null (empty) values that represent inactive plans, you could use:

CountIf([Payment], [Plan] != true)
    
                
Copy
    
    
                
Copied
    

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

To view your conditional counts by plan, set the Group by column to “Plan”.

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 your conditional count.

CountIf accepts a function or conditional statement that returns a boolean value (true or false).

Metabase

• Conditional running counts

Other tools

• SQL
• Spreadsheets
• Python

CountIf doesn’t do running counts, and CumulativeCount doesn’t accept conditions (or any other arguments) . To do conditional running counts, you’ll need to be creative: combine CumulativeSum (not CumulativeCount! ) with case. The idea is to use case to return 1 when a condition is satisfied and 0 when it isn’t, then compute the running sum of all the 1’s.

If our sample data is a time series:

And we want to get the running count of active plans like this:

Create an aggregation from Summarize > Custom expression:

CumulativeSum(case([Active Subscription] = true, 1,0))
    
                
Copy
    
    
                
Copied
    

You’ll also need to set the Group by column to “Created Date: Month”.

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

If our sample data is stored in a PostgreSQL database, the SQL query:

SELECT COUNT(CASE WHEN plan = "Basic" THEN id END) AS total_basic_plans
FROM accounts
    
                
Copy
    
    
                
Copied
    

is equivalent to the Metabase expression:

CountIf([Plan] = "Basic")
    
                
Copy
    
    
                
Copied
    

If you want to get conditional counts broken out by group, the SQL query:

SELECT
    plan,
    COUNT(CASE WHEN active_subscription = false THEN id END) AS total_inactive_subscriptions
FROM accounts
GROUP BY
    plan
    
                
Copy
    
    
                
Copied
    

The SELECT part of the SQl query matches the Metabase expression:

CountIf([Active Subscription] = false)
    
                
Copy
    
    
                
Copied
    

The GROUP BY part of the SQL query matches a Metabase Group by set to the “Plan” column.

If our sample data is in a spreadsheet where “ID” is in column A, the spreadsheet formula:

=CountIf(B:B, "Basic")
    
                
Copy
    
    
                
Copied
    

produces the same result as the Metabase expression:

CountIf([Plan] = "Basic")
    
                
Copy
    
    
                
Copied
    

If our sample data is in a pandas dataframe column called df, the Python code:

len(df[df['Plan'] == "Basic"])
    
                
Copy
    
    
                
Copied
    

uses the same logic as the Metabase expression:

CountIf([Plan] = "Basic")
    
                
Copy
    
    
                
Copied
    

To get a conditional count with a grouping column:

## Add your conditions

    df_filtered = df[df['Active subscription'] == false]

## Group by a column, and count the rows within each group

    len(df_filtered.groupby('Plan'))
    
                
Copy
    
    
                
Copied
    

The Python code above will produce the same result as the Metabase CountIf expression (with the Group by column set to “Plan”).

CountIf([Active Subscription] = false)
    
                
Copy
    
    
                
Copied
    

• Custom expressions documentation
• Custom expressions tutorial