Countif(Text within Text)

[Katana_kid]

Hello all,

I have a problem with figuring out a formula.

What I have:
I have a vacation calendar. On the Calendar I can input the names of the person taking a day off work, by entering his/her name into the cell for the day (e.g. For Jan 3rd I can put in the name Bob).

The count if formula I have is
=20-COUNTIF('2006 Calendar'!$B$5:$H$111, "Bob")

20 = is the number of maximum days allowed for vacation
'2006 Calendar'!$B$5:$H$111 = is the page and cell range of the calendar
"Bob" = is the employee

Problem:
If I type in
Bob Steve
into cell Jan 3rd, the count if formula does not recognize either Bob or Steve. People will have overlapping vacations, and I can't figure out how to have this work....

Can someone give me direction?
(I've tried the find, findb, and search, but I get a number reference.. I want to count how many times the word "Bob" appears in the whole range, regardless of where in teh cell it is.....)


Thanks in advance,
Tom

 

[Andrew Poulsom]

You can use a wildcard with COUNTIF:

=COUNTIF('2006 Calendar'!$B$5:$H$111,"*Bob*")

 

[Katana_kid]

Thank you, completed, and it works.....

me feels dumb