COUNIFS formula when multiple contacts

edited 12/09/19 in Formulas and Functions

I have a sheet with columns such as "Status of Request"(Dropdown list: New, In Design, Completed) and "Assigned Designer"(My assigned designer column allows multiple contacts). I want to count how many projects under the status "In Design" for Designer "Luis" do I have so I have the formula 

=COUNTIFS([Assigned Designer]:[Assigned Designer], "Luis", [Status of Request]:[Status of Request], "In Design")

and it works but it only counts the cell that has "Luis" as the ONLY designer, so if I have one of the projects with multiple designers it won't count it. What can I do about it?  

I have tried adding the criteria  = (equal to), <> (not equal to), > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to) but they are not accurate. I guess I should be looking for something like "has any of" 




Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!