Help! COUNTIFS Formula Logic with 2 Date Fields

Tim Möhle

I am referencing V2.0 of a shared spreadsheet that now has 2 "Created Date" fields:  1) the system generated CREATED column that will be valid for all new entries going forwards, and 2) a LEGACY CREATED DATE column that reflects when older entries were originally created (before they were imported into the new sheet.

I have a COUNTIFS formula which I use to tally the number of entries that meet all my criteria, but now need to fold in a condition that checks to see first if there is a value if the LEGACY CREATED DATE COLUMN to use that date,  if not, then use the date in the CREATED column.

e.g.  I've attached pictures of my tally sheet and sample source data.  I use a formula like this to get my tallies by month:  

=COUNTIFS({Business Unit}, "Customer Care", {Created Date}, (AND(MONTH(@cell) = 11, YEAR(@cell) = 2018)), {Request Status}, @cell = [Care Request Status]2)

Now, I need it to check to see if there's a date in the LEGACY CREATED DATE column first and if it's blank, default to the CREATED column date.

Appreciate your help.

Tim

Paul Newcome

=COUNTIFS({Business Unit}, "Customer Care", {Created Date}, (AND(MONTH(@cell) = 11, YEAR(@cell) = 2018)), {Request Status}, @cell = [Care Request Status]2, {Legacy Created Date}, ISBLANK(@cell)) + COUNTIFS({Business Unit}, "Customer Care", {Legacy Created Date}, (AND(MONTH(@cell) = 11, YEAR(@cell) = 2018)), {Request Status}, @cell = [Care Request Status]2)

 

Try something like this. Note a few changes...

1. Added to only count if the legacy created date is blank.

2. Replicated your original formula but changed it to look at the legacy created date.

3. Added them together.

 

What this does is looks down your Created Date column, looks for your original criteria and only counts it if it meets all of that AND the Legacy Created Date field is blank. It will then count your legacy created dates meeting that same criteria and add it to the first count. That will allow you to look at both columns making the legacy date the priority and avoiding duplicate counts.

Tim Möhle

Thank you!  I'll give this a try.

 

Tim