Bob's answer works for me.
I used
=SUMPRODUCT(--(ISNUMBER(SEARCH(E6,T(OFFSET(B5:B15,{0,3,9,10},0,1 ,1))))))
With "No Sales on Tue" in B5, B8, B14 and B15
and "Tue" in E6

I suggest you try in on a dummy worksheet where you have carefully typed 'No
Sales on Tue" into the cells B5, B8, B14 and B15
Then double check you have the formula correct - pasting from the email is
safe.
It does work, you need to find what is incorrect in your actual worksheet
Have a great weekend!
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Sean" wrote in message
...
Thanks Bob, this is my formula using actual ranges but it returns ), I
expect 1

=SUMPRODUCT(--(ISNUMBER(SEARCH(Z5,(OFFSET(N9:N33,{0,8,10,11,13,1 8,23},
0,1,1))))))

Z5 is where the specific text value in Range N9:N33 I am looking for
is. The value in Offset 13 contains the text that is in Z5