Using COUNTIF on table but not counting correctly??

[SNA400]

HI All

Please bear with me on the description - a bit long winded.

I have 5 tables for the different groups in our company which we use to track holidays and I am using COUNTIF to reference the persons name on each table to tally number of days each person has off.
The issue I am having is the formula is not counting those cells where there are two names in the cell for days where two people are off.

(COUNTIF('Team Leaders'!$B$2:$M$33,M2) where M2 is the employee name and B2:M33 are the calendar cell references - the same on each table

Two names in the cell are set as "EmployeeA/EmployeeB"

I have tried using a generic to see if it will work but still no joy

Is there some way to count these or separate them within the cell to allow them to count correctly?

 

[Georgiboy]

Maybe with wildcards:

=(COUNTIF('Team Leaders'!$B$2:$M$33,"*" & M2 & "*")

 

[Peter_SSs]

Maybe with wildcards:

=(COUNTIF('Team Leaders'!$B$2:$M$33,"*" & M2 & "*")

I would be wary of that as it could be that a short name is also part of a longer name as in my sample below. That formula returns 5 for Fred when it should only be 3.

@SNA400
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Possibly this?

22 12 15.xlsm
	B	C	D
2	Fred
3	Tom		Fred/Tom
4			Tom/Alfred
5		Alfred	Fred/Alfred/Ken
6		Tom
7
Team Leaders

22 12 15.xlsm
	M	N
1	Name	Count
2	Fred	3
3	Alfred	3
4	Tom	4
5	Ken	1
Count

Cell Formulas
Range	Formula
N2:N5	N2	=COUNT(SEARCH("/"&M2&"/","/"&'Team Leaders'!B$2:M$33&"/"))

 

[Georgiboy]

I would be wary of that as it could be that a short name

I made the assumption that the OP would understand that using a short name for a function like this would cause a problem, even if the names were in different cells. Personally i would be looking to restructure the spreadsheet so that each cell would not take more than one entry - this would aid analysis at a later date.

 

[SNA400]

Hi both - sorry, I used the wrong terminology - I said generic instead of wildcard so I was aware of the issue

I will give your idea a try Peter and if it works I'll owe you a drink

 

[Peter_SSs]

I will give your idea a try Peter

Hope it works for you. .. but please don't forget this:

@SNA400
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)