Array Formula to count specific text ignoring duplicates.

Hi XOR LX

Thanks for the quick response.

When i apply that to o" i get #NAME?

I am using a german version of Excel 2007 does that make a difference with the formula as excel is saying that the "," in the formula are an error and when i change them to ";" i then get the NAME error.

regards

Originally Posted by XOR LX

Apologies - I didn't fully read your subsequent paragraph:

Try this array formula in O2 and copy down (note: you have extra spacing in your "Yes" value in the drop-down menu for Overnight which will need to be removed in order for this to give correct results):

=IF(N2="No",0,SUMPRODUCT(--(MMULT((COLUMN($B$1:$J$1)^0),TRANSPOSE(--($B$2:$J$31=M2)))>0))+--(N2<>"Yes"))

Regards