Tour Tracking Formula Issue

SOmalley1124

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!

KPH

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

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)

SOmalley1124

Thank you!! :)

KPH

Happy to help!