COUNTIFS + CONTAINS + OR Formula

Elisha Wright

Hello -

Looking for some assistance on a cross sheet formula. What I'm trying to calculate is how many Level 2 rows display a specific project manager name from a multi-select column type. There are several rows with more than one name. Additionally, I want the formula to count all projects that have High, Medium, or Low displayed in another column titled "Marketing".

I am assuming I need a COUNTIFS function in addition to OR and CONTAINS to get what I need. Can all of these be used in one formula? If so, is there a specific order they need to go in? Below is the most recent version of the formula I was working on and I received an UNPARSEABLE error message.

=COUNTIFS({Level}, =2, {Project Lead}, CONTAINS("Mike Smith"), OR({Marketing}, "L", {Marketing}, "M", {Marketing}, "H"))))

TIA!

Dan W

Sorry for the late response, I was able to make this formula work on two test sheets with cross referencing, What error are you receiving?

HAS looks for an exact match. I find that it works better with multi select drop downs and contact type columns.

Here is the Help center links that explains it in full with examples. There is also a Template you can get from the Template section that has a lot of good information and practical usages of each formula.

https://help.smartsheet.com/function/has

https://help.smartsheet.com/function/contains

Dan W

Give this a try

=COUNTIFS({Level}, =2, {Project Lead}, HAS(@cell, "Mike"), {Marketing}, OR(@cell = "Low", @cell = "Medium", @cell = "High"))

Elisha Wright

Thank you for the response! I still was not able to get the formula to work here. Can @cell be used in a cross sheet formula? In your example, the "OR" section is going to be pulled from a different sheet. Also, I noticed you used HAS instead of CONTAINS. Can you explain what the difference is between the two?

I appreciate your help!

Dan W

Sorry for the late response, I was able to make this formula work on two test sheets with cross referencing, What error are you receiving?

HAS looks for an exact match. I find that it works better with multi select drop downs and contact type columns.

Here is the Help center links that explains it in full with examples. There is also a Template you can get from the Template section that has a lot of good information and practical usages of each formula.

https://help.smartsheet.com/function/has

https://help.smartsheet.com/function/contains

Elisha Wright

Hi Dan,

This was very helpful - I am getting correct data back! The columns we were pulling from were not the Contact List or multi select drop down. I changed it to that and getting all that I need back.

I appreciate your help!