MIN DATE with Conditions

Karen Hansard

Hello. I'd like to return the minimum (and maximum) date from another sheet with multiple conditions.

Detailed Sheet:

Rollup Sheet

I would like to populate the earliest Start and latest End Date in the Rollup sheet from the detailed sheet where the Request Owner is equal to request owner in rollup sheet and the status is not Complete. My formula does not work. I haven't included the Status in the formula but that would be the second condition I want to meet.

Thanks in advance.

Nick Korna

Hi @Karen Hansard,

The formula you would want would be something like this:

=MIN(COLLECT({Start Date}, {Request Owner}, [Request Owner]@row, {Status}, <>"Complete"))

Dataset (sorry about the Euro date format!):

The MAX would use much the same formula, possibly only changing the column references if you wanted the End Date instead of the Start Date.

Hope this helps, but if I've missed something or you've any questions then just post! 😊

Karen Hansard

Hi Nick,

Thanks for the quick response. It works when I only have 1 condition, but I'm having an issue when I include a 2nd condition. I get an #INCORRECT ARGUMENT error.

=MAX(COLLECT({End Date}, {Lead}, [Lead Name]@row, {Status}, <>"Complete"))

Works fine with the one condition below

=MAX(COLLECT({End Date}, {Lead}, [Lead Name]@row))

Any ideas?

K.

Genevieve P.

Hi @Karen Hansard

Your formula structure looks correct - is it possible that your {Status} column has an error in any of its cells? If so, this will bubble up into any other formula referencing it.

You could also try adding an @cell before the <> "Complete" :

=MAX(COLLECT({End Date}, {Lead}, [Lead Name]@row, {Status}, @cell <>"Complete"))

However it should work without that.

Let us know if the {status} column could be the culprit!

Cheers,

Genevieve

Karen Hansard

Hi Genevieve. That worked. Thanks so much.