sumif with truly approximate text match

**nrff** · 01-24-2019, 10:19 AM

Hi,

I have the following data

A B C D
1 Video Game Consoles 2 Gaming
2 Video Game Handhelds 3 0

I would like cell D2 to report the sum from column B of the items in A that approximately match in text the text in cell D1.

=SUMIF(A1:A2,"*"&D1&"*",B1:B2)

I cannot predict the text in D2 so not fair to change the formula in D2 to search for "Gam" rather than "Gaming". It could also be "Automation" in D1 and "Automatic" in A1:A2 for example. Is there a way to get sumif to search in a truly approximate way?

Thanks for your help!

**Bo_Ry** · 01-24-2019, 10:52 AM

How about

=SUMIF(A1:A2,"*"&LEFT(D1,LEN(D1)-3)&"*",B1:B2)

**nrff** · 01-24-2019, 11:06 AM

Well,

=SUMIF(A1:A2,"*"&LEFT(D1,LEN(D1)-3)&"*",B1:B2)

would work but would also mean already knowing the content of D2 and how similar it is to the content of the columns to search within. As I said, content of D1 might change so chopping the content of D1 in a predictive way wouldn't really work when expanding this example to other words.

**Bo_Ry** · 01-24-2019, 11:29 AM

Please provide more sample, I may come up with something better.

**nrff** · 01-24-2019, 11:33 AM

"Automation" in D1 and "Automatic" in A1:A2 for example

Thanks for your help!

**nrff** · 01-24-2019, 11:34 AM

really after a general "approximate" option like the FALSE in vlookup. Not sure there are any wildcards in sumif for this purpose ...

**nrff** · 01-24-2019, 11:57 AM

the content of D1 might also come from having split a sentence in another cell by using the space as delimiter. So it could be something like "Gaming:", i.e. the number of characters from D1 to consider in the actual sumif would be unpredictable.

sumif with truly approximate text match

LinkBack

Thread Tools

Rate This Thread

Display

sumif with truly approximate text match

Re: sumif with truly approximate text match

Re: sumif with truly approximate text match

Re: sumif with truly approximate text match

Re: sumif with truly approximate text match

Re: sumif with truly approximate text match

Re: sumif with truly approximate text match

Thread Information

Users Browsing this Thread

Similar Threads

Match Text to an Approximate Match

Approximate match to return text 'match' rather than the whole approximate

[SOLVED] Match Index to an approximate value

Need first approximate match returned

match approximate 2 column

[SOLVED] Locate approximate match

Find Approximate Match

Bookmarks

Bookmarks

Posting Permissions