Validate duplicate with 3 variables

Hi guys,

I have been working since days in one formula, what I call "cascate formula", to validate 4 variables to define if an event require attention or not. An event can only be validated, if there is NO other event assigned to the same trainer in the same time frame (start and end date).

So, I have 4 columns (start date, end date, event and assigned to). I created some phantom columns (columns to receive some values that will be hidden). So, I tried:

Phanthom1: =IF(COUNTIF([Start Date]:[Start Date], [Start Date]1) > 1, 1, 0), but then I do not know how to cascate in the best way.

I also tried: Phantom2 =IF(AND(COUNTIF(Event:Event, Event1) > 1, COUNTIF([Start Date]:[Start Date], [Start Date]1) > 1, COUNTIF([Assigned To]:[Assigned To], [Assigned To]1) > 1), 1, 0), but it also did not work.

Can some of the masters of smartsheet help me here?

Thanks for your comprehension and support

Best Regards


Best Answer


  • L_123
    L_123 ✭✭✭✭✭✭

    try using a countifs

    =if(countifs(Event:Event, Event@row, [Start Date]:[Start Date], [Start Date]@row, [Assigned To]:[Assigned To], [Assigned To]@row)> 1,1,0)

  • Hi guys,

    thank you very much.. that helped a lot.

    @L@123: your formula did not entirely worked for the main objective: evaluate if there would be any duplicity in dates for the same trainer, what the formula from Paul did it very well.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!