Find some text but not others
 

Hi.
I'm using this formula
=SUMPRODUCT(--(ISNUMBER(FIND(A1,$B$1:$B$30--($C$1:$C$30)) multiple times.
i.e. finding A2, A3 etc. Where A1 is text within a phrase. e.g. finding
"diesel", "unleaded", or "fuel" within phrases in B column like "bulk diesel
fuel", "fuel unleaded" etc
Is there an easy way to avoid overlaps for words in A1,A2...A10 without
writing a massive formula. ie sum A1's related value in C1 but not A2...A10 =
sum "diesel" but not "fuel" etc.

Thanks.

Find some text but not others
 

I'm not sure, but doesn't this in say, D1:
=SUMPRODUCT(--(ISNUMBER(FIND(A1,$B$1:$B$30))*$C$1:$C$30))
return the desired results for you, for the text input in A1, eg: diesel ?
Ie it'll sum up col C where the phrases in col B contain: diesel

If you need it "stricter", perhaps try improving the text string in A1 to
say: diesel fuel. Or use: fuel unleaded, instead of just: fuel, as another
example.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:62
xdemechanik
---
"Mark11" wrote:
I'm using this formula
=SUMPRODUCT(--(ISNUMBER(FIND(A1,$B$1:$B$30--($C$1:$C$30)) multiple times.
i.e. finding A2, A3 etc. Where A1 is text within a phrase. e.g. finding
"diesel", "unleaded", or "fuel" within phrases in B column like "bulk diesel
fuel", "fuel unleaded" etc
Is there an easy way to avoid overlaps for words in A1,A2...A10 without
writing a massive formula. ie sum A1's related value in C1 but not A2...A10 =
sum "diesel" but not "fuel" etc.