Creating a Sumif formula based on Distinct field Values

[leale1997]

I feel like this should be easy but I am just missing something.  I have tried aggr and sum and can't get this formula to work out correctly.

I have a data table that looks like the table below.  I would like to calculate the number of days a record that is not closed has been opened based on today's date and Distinct record ID's.  I have tried using variations of this formula:

sum(distinct if([Status]='Urgent' and isnull(DateClosed), today()-DateCreated, [RecordID]))

RecordID	DateCreated	Status	DateClosed
1	02/05/16	Urgent	02/25/16
2	01/01/17	Routine
3	07/05/16	Routine	08/04/16
4	09/10/16	Routine
5	02/10/16	Urgent
6	01/06/17	Routine
7	07/10/16	Routine
8	09/15/16	Routine
9	02/15/16	Routine	03/11/16
10	01/11/17	Routine
11	07/15/16	Routine
12	09/20/16	Routine	11/04/16
13	02/20/16	Urgent
14	01/16/17	Routine
15	07/20/16	Urgent	09/18/16
16	09/25/16	Urgent

[sunny_talwar · Accepted solution]

May be this:

Sum({<RecordID = {"=Len(Trim(DateClosed)) = 0"}, Status = {'Urgent'}>} Today() - DateCreated)

[leale1997]

Of course that's the answer ?#$%^&? LOL!  Thanks so much! 

[leale1997]

Hi Sunny!

You were such a help with my last question. Wonder if you might field another. I would like to do the same thing except leave out the second criteria.

So I think it would look like this:

Sum({} Today() - DateCreated)

But it’s not working. What am I missing?

[sunny_talwar]

Leave which criteria? Closed Date or Status?

[leale1997]

Leaving closed date is null.

But not considering Status as a criteria at all.

In other words, summing today()-datecreated for distinct record ids that do not have a populated dateclosed field.

The record id field may contain duplicates.

[sunny_talwar]

This

Sum({<RecordID = {"=Len(Trim(DateClosed)) = 0"}>} Today() - DateCreated)

[leale1997]

Yes that worked! Thanks so much. I realize now I left out the ‘>’.

Way appreciate the assist. )

[sunny_talwar]

Sounds good