Count Instances of Text in String Q

[Sean]

How could I formulate a formula that will count the number of times a
cell value appears within a text string in non contiguous range.

Example A1 contains the value "Tue". I wish to add the number of times
A1 appears in ranges B5;B8;B14;Bk15.

The actual text in B5;B8;B14;Bk15. will be "No Sales for Tue"

 

[Bob Phillips]

I assumed that BK15 was meant as B15

=SUMPRODUCT(--(ISNUMBER(SEARCH("Tue",T(OFFSET(B5:B15,{0,3,9,10},0,1,1))))))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Sean]

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,18,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

 

[Sean]

Thanks Bob, this is my formula using actual ranges but it returns ), I
expect 1

That ) character should read 0

 

[Bernard Liengme]

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

 

[Sean]

Thanks Bernard, what is the T part of the formula for... T(OFFSET(.. ?

 

[David Biddulph]

For T as with every other Excel function (except DATEDIF), if you click on
the fx link beside the formula box, it will give you a link to Excel help on
the function.