# Using COUNTIF on table but not counting correctly??



## SNA400 (Dec 15, 2022)

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 (Dec 15, 2022)

Maybe with wildcards:

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


----------



## Peter_SSs (Dec 15, 2022)

Georgiboy said:


> 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.xlsmBCD2Fred3TomFred/Tom4Tom/Alfred5AlfredFred/Alfred/Ken6Tom7Team Leaders

22 12 15.xlsmMN1NameCount2Fred33Alfred34Tom45Ken1CountCell FormulasRangeFormulaN2:N5N2=COUNT(SEARCH("/"&M2&"/","/"&'Team Leaders'!B$2:M$33&"/"))


----------



## Georgiboy (Dec 15, 2022)

Peter_SSs said:


> 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 (Dec 15, 2022)

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 (Dec 15, 2022)

SNA400 said:


> I will give your idea a try Peter


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


Peter_SSs said:


> @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’)


----------

