I am migrating a sales report from Excel to Tableau and I'm struggling to find a direct COUNTIF function. In Excel, I simply use =COUNTIF(Range, ">500") to count high-value transactions. How do I achieve this same result in Tableau? I tried wrapping an IF statement inside a COUNT, but I keep getting errors about mixing aggregate and non-aggregate arguments. What is the correct syntax for a conditional count that updates dynamically as I filter my dashboard?
3 answers
Tableau doesn't have a specific COUNTIF function because it handles logical operations differently than cell-based spreadsheets. To replicate this, you use a row-level IF statement nested inside an aggregate function. For your specific example, the formula would be: COUNT(IF [Sales] > 500 THEN [Order ID] END).
This works because the IF statement evaluates every row first; if the condition is met, it returns the ID, and if not, it returns a NULL. Since Tableau's COUNT function automatically ignores null values, the result is exactly like a COUNTIF. If you only want to count unique entries (like unique customers who spent over 500), simply swap COUNT for COUNTD. Just ensure you aren't trying to use an already aggregated field (like SUM(Sales)) inside that row-level IF statement, as that will trigger the "mixing aggregate and non-aggregate" error.
That row-level approach makes sense for simple counts, but what if I need the count to remain static regardless of what dimensions I drag into my view? If I want to count the total number of orders over 500 across the entire region, even if I'm looking at individual product categories, would I need to use a Fixed Level of Detail (LOD) expression for that?
The simplest way I found is to just create a boolean field first, like [High Value] = [Sales] > 500, and then drag that onto your Filters shelf or use it in a simple SUM(INT([High Value])) calculation.
I agree with Jennifer! Converting booleans to integers with INT() is such a clean way to handle counts in Tableau. It makes the formula much easier to read for other team members who might be auditing your workbook later, and it keeps your data pane organized.
Michael, you're exactly right. If you want the count to be independent of your viz level of detail, you'd use: { FIXED [Region] : SUM(IF [Sales] > 500 THEN 1 ELSE 0 END) }. Using the 1 and 0 logic inside a SUM is a common pro-tip in the Tableau community because it’s often faster for the engine to process than returning full IDs, and it allows you to "fix" that value to a specific dimension.