Need Help with Formulas (Assigned To More than 3 Projects)

Stephanie T

I should start off by saying I am very new to Smartsheets and have little experience. I am trying to add a formula(s) to eventually use conditional formatting in Ganett view to highlight the bars red if a employee(Assigned To) is assigned to 3 or more projects with overlapping dates per state. Is this even possible??

Here is an example of the sheet I am working on

Paul Newcome

Insert a flag column and use this formula:

=IF(COUNTIFS([ASsigned To]:[ASsigned To], @cell = [Assigned To]@row, Start:Start, @cell<= Finish@row, Finish:Finish, @cell>= Start@row)> 3, 1)

Then you can set up your conditional formatting based off of this flag column.

Stephanie T

Thank you Paul. Is this also accounting for the state?

Paul Newcome

I missed that you needed the state as well. My apologies. You would just need to add another range/criteria set to the COUNTIFS to look at the State column and evaluate for State@row.