Countif w/semicolon separated values AND Filter

[crazymfr]

I was given a function that allowed me to count unique semicolon separated
values in aggregate column cells. I want to be able to couple this with the
sumproduct "countif" function that allows for filtering. I have used each
separately, but would like to use them together.

This would greatly enhance my ability to understand my data in a more
efficient way.

Sample Data Set: (I would like to show aggregate totals for each Day that is
given AND filter by Department)
Department (Col#1) Day of the week (Col#2)
Marketing Wednesday
Personal Lines Monday; Tuesday; Wednesday; Thursday
Quest Tuesday; Thursday; Friday
Client Services Tuesday; Wednesday; Thursday
Sales Monday
Programs Monday; Tuesday; Wednesday; Thursday
Marketing Thursday
Quest Tuesday; Wednesday
Sales Tuesday; Wednesday; Thursday
Claims Tuesday; Wednesday; Thursday
Inside Sales Monday; Wednesday; Thursday
Quest Tuesday; Friday
Personal Lines Thursday

 

[T. Valko]

Try this:

J1 = departemnt
K1 = Monday

=SUMPRODUCT(--(A1:A100=J1),--(ISNUMBER(SEARCH(K1,B1:B100))))