Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find repeated text in a text document in Excel | Excel Worksheet Functions | |||
Search, find or lookup defined text in text string | Excel Worksheet Functions | |||
find text in cell and replace it with part of the text in that ce. | Excel Discussion (Misc queries) | |||
Find text within cell then display text to left | Excel Discussion (Misc queries) | |||
can you find specific text in a string ignoring any other text | Excel Discussion (Misc queries) |