Using SUMIFS with multiple OR criteria

First time posting here - stuck on a problem!

I want to SUM a numerical column if the corresponding text column contains one of several strings of text.

By way of example, if the word "hotdog" appears in column E, I want to sum the corresponding numbers in column D

This works well enough if I use: =SUMIF(E:E, "*hotdog*",D:D)

Where I am getting stuck is if I try to sum cells with multiple alternative words. For example, if the strings "hotdog" OR "burger" appear in column E, I want the sum of column D.

All of the solutions I have found on the Internet so far ignore one twist: If a cell contains BOTH "hotdog" AND "hamburger" then I only want to account for the number in column D once. For this reason, the following formula is not working: =SUM(SUMIFS(D:D,E:E,{"*hotdog*","*burger*"})) That formula seems to work up until it finds a cell containing both strings, and then it doubles the column D output.

Who has ever addressed this before? Thanks!

Well, why not subtract from your formula the numbers when both hotdog and burger appear together in column E, i.e.:

=your_formula -SUMIFS(D:D,E:E,"*hotdog*",E:E,"*burger*")

as you will have double-counted the values from column D when both occur.

Hope this helps.

Pete

Oh, excellent! I think that will work when I only have two criteria (hotdog & burger).

If I have more than that, any suggestions? For example, if I want to return the sum of column D if column E contains the strings "hotdog" OR "burger" OR "popcorn" OR "chips"

Regardless of the number of those keywords that appear in the cell, I only want to count column D once. Unfortunately, I don't think your great solution will work in that situation. Got another work-around?

Another great suggestion, daddylonglegs! I will test it out now!

If you have 3 words, then you will have triple-counted the values in column D when all 3 occur together, so you will need to subtract two times the sum from all three criteria. However, you will have double-counted when word1 and word2 occur together, when word2 and word3 occur together, and when word1 and word3 occur together, so you will need to subtract these sums (once).

For 4 conditions, you will have quadruple-counted when all 4 occur together, so subtract three times the sum. Also, subtract two times the sum of any 3 words together, and single-subtract any 2 words together - it gets a bit messy ...

Hope this helps.

Pete

Thank you both for your help. Daddylonglegs' suggestion ultimately won the day. Pete - your suggestion would have worked but, you're right, it would have gotten messy!

I wish there was a more straightforward SUMIFS with OR function. Tons of people seem to struggle with it, and I am just now learning about arrays.

Thanks again!

Ah well, I'm glad you got a solution which worked for you.

Pete