My current formula
=COUNTIFS($A$2:$A$11,$A1,$D$2:$D$11,"*manager*")*(COUNTIF($A$2:$A$11,$A1)>1)
Highlights the EE Number cells if a specific employee number has more than one Job title and if one of those job titles contains the word "Manager". How would I change it so it highlights the EE Number cells if a specific employee number has more than one job title with one of them containing the word "manager" and another containing the word "cashier". I tried changing my formula to this:
=COUNTIFS($A$2:$A$11,$A1,$D$2:$D$11,"*manager*")*(COUNTIF($A$2:$A$11,$A1,$D$2:$D$11,"*cashier*")>1)
but it gives me the error you have entered too many arguments for this function . Is this possible to do in conditional formatting?
COUNTIF can only take 2 parameters. You need to use COUNTIFS, like in the first part of your currently working formula. What you actually can do is add the cashier part right after the manager part:
=COUNTIFS($A$2:$A$11,$A1,$D$2:$D$11,"*manager*",$D$2:$D$11,"*cashier*")*(COUNTIF($A$2:$A$11,$A1)>1)
The above will highlight instances where the job name contains both manager and cashier at the same time. If you want to highlight instances where the job name contains either manager or cashier or both, then you can try:
=(COUNTIFS($A$2:$A$11,$A1,$D$2:$D$11,"*manager*")+COUNTIFS($A$2:$A$11,$A1,$D$2:$D$11,"*cashier*"))*(COUNTIF($A$2:$A$11,$A1)>1)
EDIT: Overlooked manager on at least one row and cashier on a different row, which would lead to:
=COUNTIFS($A$2:$A$11,$A1,$D$2:$D$11,"*manager*")*COUNTIFS($A$2:$A$11,$A1,$D$2:$D$11,"*cashier*")*(COUNTIF($A$2:$A$11,$A1)>1)