Tour Tracking Formula Issue

Hello,

I am currently using a formula to track how many tours each tour guide has led. I have their names on a sheet next to each tour they have been assigned to. I am using =COUNTIF({Tour Database Range 1}, "Name") and inserting each person's name into the "name" portion of the function to get the count. I noticed that the numbers are too low and discovered the formula isn't counting when there is more than one guide listed as the tour guide. Is there something additional that can be added to this formula so that it counts when there is a second person listed in the tour guide column?

Thank you!

Best Answer

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    Hi @SOmalley1124

    Your formula will count if the value/text in cell in the range you selected is equal to whatever you put between the quotation marks. As you have found, if the cell has more than one name it in, then it will not equal and not be counted.

    So instead of the criteria being "Name" you need another function. A FIND function like this one:

    FIND("Name", @cell) > 0

    This will find rows where the Name is in the cell more than 0 times.

    Popping that into the COUNTIF gives you this:

    =COUNTIFS({Tour Database Range 1}, FIND("Name", @cell) > 0)

    Illustrated example...

    If you column looks like this

    This would result in 2

    =COUNTIFS({Tour Database Range 1}, FIND("Bob", @cell) > 0)


    And this would be 3

    =COUNTIFS({Tour Database Range 1}, FIND("Bill", @cell) > 0)


    And this would be 1

    =COUNTIFS({Tour Database Range 1}, FIND("Bertha", @cell) > 0)

Answers

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    Hi @SOmalley1124

    Your formula will count if the value/text in cell in the range you selected is equal to whatever you put between the quotation marks. As you have found, if the cell has more than one name it in, then it will not equal and not be counted.

    So instead of the criteria being "Name" you need another function. A FIND function like this one:

    FIND("Name", @cell) > 0

    This will find rows where the Name is in the cell more than 0 times.

    Popping that into the COUNTIF gives you this:

    =COUNTIFS({Tour Database Range 1}, FIND("Name", @cell) > 0)

    Illustrated example...

    If you column looks like this

    This would result in 2

    =COUNTIFS({Tour Database Range 1}, FIND("Bob", @cell) > 0)


    And this would be 3

    =COUNTIFS({Tour Database Range 1}, FIND("Bill", @cell) > 0)


    And this would be 1

    =COUNTIFS({Tour Database Range 1}, FIND("Bertha", @cell) > 0)

  • KPH
    KPH ✭✭✭✭✭✭

    Happy to help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!