# SUMIF, conditional sum, mixing aggregate and non-aggregate

**Justin Larson**Jan 14, 2013 2:23 PM

I'm embarrassed to say I've been using Tableau for nearly a year and still can't figure out how to do this:

How can I get a calculation to work like sumif does in Excel? Once I have this figured out, it should work for any conditional calculation (maxif, minif, countif, averageif, etc)

The classic example I see is people structuring a calculation like this:

AVG(IF [Category]='New' THEN [Units] END)

or

AVG(IIF([Category]='New',[Units],0))

Where handling the return of Nulls is the difference between the two.

Ok. That works when the only "if" is looking at Category="NEW". Yes, you could just add conditions with a string of elseifs, but what if there are a thousand different values in "Category"? What if Category is actually "CustomerID" and not only are there lots of values, but they will change with each refresh? In excel, this is equivalent of using:

=sumif([Category],"NEW",[Units])

or worse

=sumif([CustomerID]="123456",[Units])

What I'm after is sumif like any Excel user does it, where the calculation would look like this:

=sumif([Category],[@[Category]],[Units])

Where [Category] is the whole field, and [@[Category]] is the value in that field in this record.

This is, essentially what Tableau considers mixing aggregate and non-aggregate conditions.

The nearest I could come up with in Tableau is:

sum( if [Category]=[Category] then [Units])

maybe

sum( if ATTR([Category])=[Category] then [Units]) ?

which of course doesn't do anything. It's the same as sum([Category]) and relies on what is in the table view because [Category] always equals [Category].

To phrase this another way, I want to partition the dataset to a subset using a field (like [Category] or [CustomerID]) and do the calculation just on that subset, but pass through a value from the row as a variable, rather than hardcoding it as a string argument. To be clear, I understand that this is a super-simple calculation if the [Category] or [CustomerID] is in the view, but I need the calculation to work the same whether or not that field is in view.

Use case:

I have a transactional database. There are four fields: CustomerID, Category, PricePaid, PurchaseDate

I need a calculation that shows the average customer lifetime (Max(purchasedate)-Min(purchaseDate) {calculated by customer using CustomerID}.

I need a calculation showing the lifetime value of a customer (sumif([CustomerID],[@[CustomerID]],[PricePaid])

I need to calculate the average number of days that has passed since each customer's most recent purchase. (today()-max(PurchaseDate))

Lot's of other uses, you get the idea.

Anyone know what I'm getting at?