# SUM with multiple conditions

Options
Member

Hello everyone,

I am having trouble with a DOMO Beast mode sum Calculation. Below you will find my existing calculation. I am looking to add the value of all records that have a date to Customer in column A and exclude those records in column B that have a payment date. Essentially I am looking to calculate the value of invoices sent to the Customer and of those who has not submitted payment yet.

Issue: It appears that I am still adding values to my total when payment dates are present.

SUM(

CASE

WHEN ` Date To Customer` IS NOT NULL AND `Payment Date` IS NULL THEN (`USD`)

END

)

Tagged:

• Coach
Options

Typically when I have a complex beastmode that isn't functioning as expected I'll break it down into different beast modes and toss them onto a table card to help investigate each portion.

Does your payment check return what you're expecting on it's own without the SUM for each record?

```CASE WHEN `Payment Date` IS NULL THEN `USD` END
```

```CASE WHEN `Date To Customer` IS NOT NULL THEN `USD` END
```

```CASE WHEN `Date To Customer` IS NOT NULL AND `Payment Date` IS NULL THEN `USD` END
```

**Did this solve your problem? Accept it as a solution!**
• Coach
Options

Agree with ﻿@GrantSmith﻿ . I would also add in an ELSE statement into the test logic and the final logic to ensure that it is evaluating the NULLS correctly. You could have blanks instead of nulls which would cause this statement to not return the results you are expecting. If you are actually have blanks you could do something like this:

```SUM(
CASE
WHEN ` Date To Customer` '1/1/1900'  AND (`Payment Date` IS NULL OR TRIM(`Payment Date`) = '')  THEN (`USD`)
ELSE 0
END
)
```

Hope this helps.

**Check out my Domo Tips & Tricks Videos

**Make sure to any users posts that helped you.
• Coach
Options

@user066906 are your invoices and payment records on the same row? or are they spread across rows? if it's spread across rows these solutions we're proposing won't work for you... so ... make sure to double check.

@MarkSnodgrass 's checks for is null OR is blank are perfect, but just do the opposite check as proposed by @GrantSmith for the dateToCustomer

```sum(
case
when (`date to customer` is not null AND trim(`date to customer`) <> '') AND (`Payment Date` IS NULL OR TRIM(`Payment Date`) = '')  THEN `USD`
end
)
```

Note the difference between AND and OR.

most of the time, you don't need ELSE 0 when you're aggregating.

Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨‍💻

**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
• Member
Options

Hi, I tried your solution and I still receive the same results. It looks like I am still pulling in blank values for 'date to Customer' and payment dates with values. I have tried many variations of the suggestions and still seem to get the same results.

All of the data for each record is in the same row.

Do you have any other ideas?

• Coach
Options

the code that everyone gave you is correct. the problem is in your understanding of your data and how you're testing your data.

if you can't identify your data with s not null and empty string, then it must contain SOMETHING that you aren't testing for. figure out what the something is.

alternatively, post a sample of your data.

Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨‍💻

**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"