8 Replies Latest reply on Sep 11, 2018 2:53 PM by Peter Fakan

# Sumifs statement in 10.5

Hi All,

I am new user to Tableau with a background in finance.

I have created the below formula in an attempt to sum column "amount' if column "ledger" = budget and if column "account" =7820 Or "account =7831.

SUM(IF [Ledger]= "BUDGET" and [Account] = 7820 OR [Account]= 7831 Then [Amount] ELSE 0 END)

The formula is valid, however when looking into the data it appears that some amounts with "ledger" actual are pulling into the calculation.

The second calculation is to sum the amount if account is not equal to "account" 7820 Or 7831.

SUM(IF [Ledger]= "BUDGET" and [Account] != 7820 OR [Account]!= 7831 Then [Amount] ELSE 0 END)

This formula is also valid, however actual values and "account" with 7820 and 7831 are pulling into the calculations. I am assuming this formula has the same error as the first.

If anyone has the solution for this and could share it would be greatly appreciated.

I have attached a copy of the workbook for reference.

Thanks,

Jeff

• ###### 1. Re: Sumifs statement in 10.5

Hi Jeff,

Is this what you are looking for ?

It was just a bracketing issue (bracket the account = 7820 OR 7831 to make them both evaluate against 'budget')

HTH

Peter

• ###### 2. Re: Sumifs statement in 10.5

Like This?

Thanks

Deepak

If it Helps, Pl mark it Helpful and CORRECT to Close Thread

• ###### 3. Re: Sumifs statement in 10.5

HI Jeff,

Your calc is correct only , you have just missed the "()" in your "if" logic.

SUM(IF [Ledger]= "BUDGET" and ( [Account] = 7820 OR [Account]= 7831) Then [Amount] ELSE 0 END)

With your logic the calculation is summing up all the amount where in a row  ledger ="Budget"and  [Account] = 7820.

OR Account=7831 , as shown below.

• ###### 4. Re: Sumifs statement in 10.5

Thanks Peter! the bracketing to evaluate budget definitely makes sense. I tried the same approach on the exclude formula, however it still seems to be pulling in 7820 & 7831. Any suggestions?

SUM(IF [Ledger]= "Budget" AND ([Account] != 7820 OR [Account] != 7831) THEN [Amount] ELSE 0 END)

• ###### 5. Re: Sumifs statement in 10.5

Thanks for the response Deepak! The first calculation works well, however the second appears to still be pulling in account 7820 and 7831. I would expect the total for fitness in the second calculation to equal \$1535.

Thanks,

Jeff

• ###### 6. Re: Sumifs statement in 10.5

Hi Jeff,

Sorry I figured the next equation would be simple after providing the first solution !

Here's a couple of solutions to the second part;

HTH

Peter

• ###### 7. Re: Sumifs statement in 10.5

Hi Peter,

This is great. Thank you! Hopefully I will get use to the logic quickly.

Cheers

Jeff

• ###### 8. Re: Sumifs statement in 10.5

Hi Jeff,

No problems - appreciate if you could mark something correct and close the thread so that it can assist others

Peter