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"

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" wrote in message
...
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"

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

On Feb 24, 11:05*am, Sean wrote:
Thanks Bob, this is my formula using actual ranges but it returns ), I
expect 1

That ) character should read 0

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

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

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.
--
David Biddulph

"Sean" wrote in message
...
Thanks Bernard, what is the T part of the formula for... T(OFFSET(.. ?