SUMIF AND OR sum column only if corresponding cells from other 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

[kvsrinivasamurthy]

ARRAY Formula for milk OR juice

Please Login or Register  to view this content.

ARRAY Formula for max and milk OR soda OR juice

Please Login or Register  to view this content.

[DonkeyOte]

Rather depends on how specifics - assuming milking should not be a match for milk then you need to first append the strings with a common delimiter to avoid false positives - that would rule out likes of SUMIF(S) so you're left with Arrays / Sumproduct, e.g:

Please Login or Register  to view this content.

but that said I think I am having a brain freeze as there has to be a more elegant approach to handling the OR element...

[kmanbob]

these formulas are working very nicely indeed...

so... can you explain what you are talking about appending strings and delimiters etc?

I have run into in instance where I want to search for only the letters "KS" or "ks" or "Ks" etc. stand alone, and not have it mistake every word that contains ks such as "trucks"

Is it not possible to specify how specific you want the search to be with/out wild cards etc ?

THANK YOU

[kmanbob]

thanks for that

[Lemice]

Hello,

You can try this slightly shorter Array formula

Please Login or Register  to view this content.

Notice that I did a small trick adding space in the searching letter, and add space in front of and at the end of all cell in searching range. This should solve the problem of "ks" in "trucks".

Edit: I just noticed that whatever I did was exactly the same as what DonkeyOte did on post #3, you should definitely check it out.

[kmanbob]

Hi DonkeyOte, can you just explain what the SIGN is for in the formula and why it is needed? I notice that the other formulas do not use SIGN infront of ISNUMBER(SEARCH(...

[DonkeyOte]

In native XL (ie. ignoring VBE) Boolean TRUE & FALSE when coerced by mathematical operators equate to 1 & 0 respectively - e.g:

=(1=1)+0

becomes 1

All of the formulae provided thus far utilise the above concept in so far as all coerce Boolean results to establish the total count of matches.

On to the use of SIGN...

To cater for the OR based elements - ie count as 1 if either "milk" or "juice" appear and not 2 if both appear - we test to see if aggregation of booleans is > 0
(in Arrays and SUMPRODUCT "AND" based tests are performed via multiplication and, "OR" based tests via addition)

In the arrays the >0 test is performed via an embedded IF (in part necessitating the Array) whereas in the SUMPRODUCT example SIGN was used to provide the appropriate multiplier for the AND element with the preceding addition having accounted for the OR
i.e. SIGN of the OR based tests will be 1 if when aggregated they exceed 0 regardless of actual total

Note:
The SUMPRODUCT is no more efficient than the Arrays and indeed can be less so depending on formula - neither are efficient!
The SUMPRODUCT does not require Array entry - it's really up to the user which they prefer to use.