SUMIF AND OR sum column only if corresponding cells from another column(s) contain word(s)

[kmanbob]

Hello Excel geniuses


Here is my predicament ...


A1 = 1
A2 = 2
A3 = 3
A4 = 4


B1 = max milk juice
B2 = max juice
B3 = max soda
B4 = milk


Ok...


1. What is the formula to SUM column A IF corresponding cell in column B CONTAINS the word "milk" OR "juice"
...my desired result here would be the sum of A1+A2+A4 = 7


2. What is the formula to SUM column A IF column B CONTAINS the word "max" AND either "milk" OR "soda" OR "juice"
...my desired result here would be the sum of A1+A2+A3 = 6

 

[ChrisR]

Probably a better way to do this but...........

=SUMIF($B$1:$B$10,"juice",$A$1:$A$10)+SUMIF($B$1:$B$10,"Milk",$A$1:$A$10)+SUMIF($B$1:$B$10,"max Milk",$A$1:$A$10)+SUMIF($B$1:$B$10,"max Juice",$A$1:$A$10)+SUMIF($B$1:$B$10,"max Soda",$A$1:$A$10)

 

[ChrisR]

Apologies.........I've just noticed you need a contains............so the above may not be what you need.......

 

[AlphaFrog]

These are array formulas. Confirm with Ctrl+Shift+Enter

1.)
=SUM(IF(ISNUMBER(SEARCH("Milk",B1:B4))+ISNUMBER(SEARCH("Juice",B1:B4)),A1:A4))

2)
=SUM(IF(ISNUMBER(SEARCH("Max",B1:B4))*(ISNUMBER(SEARCH("Milk",B1:B4))+
ISNUMBER(SEARCH("Soda",B1:B4))+ISNUMBER(SEARCH("Juice",B1:B4))),A1:A4))

The + is equivalent to OR
The * is equivalent to AND

 

[ChrisR]

Again, Probably a better way to do this, but you could use this and adapt it for each name by adding a + to the end of each formula and adding the next adapted one (Enter it using Ctrl + Shift + Enter)

=SUM(IFERROR(IF(FIND("Milk",C1:C5),A1:A5,0),0))

 

[kmanbob]

Thank you very much, these work nicely and are the ones I am using...

1. a little off topic, but how do I specify exact word or characters in this formula, such as "lk" only, for instance if I had another cell B5 = LK, & I wanted to search for the LK by itself, I don't want the formula to mistake "LK" in "milk"

These are array formulas. Confirm with Ctrl+Shift+Enter

1.)
=SUM(IF(ISNUMBER(SEARCH("Milk",B1:B4))+ISNUMBER(SEARCH("Juice",B1:B4)),A1:A4))

2)
=SUM(IF(ISNUMBER(SEARCH("Max",B1:B4))*(ISNUMBER(SEARCH("Milk",B1:B4))+
ISNUMBER(SEARCH("Soda",B1:B4))+ISNUMBER(SEARCH("Juice",B1:B4))),A1:A4))

The + is equivalent to OR
The * is equivalent to AND

 

[AlphaFrog]

Depending on the nature of the data, you could do one or both of the following...

• Replace SEARCH with FIND and it will be Case Sensitive.
• Search for something like " lk " and add a spaces before and after the data;
  ISNUMBER(SEARCH(" lk "," "&B1:B4&" "))