COUNTIFS with multiple contacts in the same cell then reference a second column


=COUNTIFS([24-7 PM]:[24-7 PM], HAS("Jon Cornwell", @cell), [Project Type]:[Project Type], "LV Switchgear")

I am trying to create a summary which shows how many of a certain project type a project manager may be involved in. Hope that makes sense

I can get the first part of the formula to work on its own..

=COUNTIFS([24-7 PM]:[24-7 PM], HAS("Jon Cornwell", @cell))

but as soon as I add the criteria for the second column, namely the Project Type, it returns a 0 value

Please see picture below.

Can anybody help please?



  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    edited 01/05/24

    Try it like this:

    =COUNTIFS([24-7 PM]:[24-7 PM], FIND("Jon Cornwell", @cell) >0, [Project Type]:[Project Type], "LV Switchgear")

    Edit - this should also work:

    =COUNTIFS([24-7 PM]:[24-7 PM], HAS(@cell, "Jon Cornwell"), [Project Type]:[Project Type], "LV Switchgear")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!