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"))))


Best Answer


  • Dan W
    Dan W ✭✭✭✭✭

    Give this a try

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

  • Elisha Wright
    edited 07/20/22

    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
    Dan W ✭✭✭✭✭
    Answer ✓

    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.

  • 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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!