SUMIF subtraction with multiple conditions (OR)

[Flash_82]

Hi everyone,

I'm having another issue where I want to add up the stock (B8:B107) if it needs to be handled with care, marked with "x" (V8:V107), a simple SUMIF, I got that covered with =SUMIF(F8:F107;"x";B8:B107). Now I don't want to add the stock if it meets other conditions in other columns, let's say if the stock is indoor (marked with an "in" in C8:C107) or if it's a liquid (marked with "lq" in D8:D107), so they need to be substracted from the first sum.

So basically I used the first SUMIF and subtracted the first condition with a new SUMIFS and added another SUMIFS for the second condition, naturally it subtracts twice, but it needs to substract it once
=SUMIF(F8:F107;"x";B8:B107)-SUMIFS(B8:B107;V8:V107;"x";C8:C107;"in")-SUMIFS(B8:B107;F8:F107;"x";D8:D107;"lq")

I tried putting the conditions in one big SUMIFS but this would imply it would only substract if the product is a liquid AND if it's stored inside, but I want to substract when it's either liquid OR indoor
=SUMIF(F8:F107;"x";B8:B107)-SUMIFS(B8:B107;F8:F107;"x";C8:C107;"in";D8:D107;"lq").

My question, how do I add the products to be handled with care, except if they are a liquid or if they are stored inside. A file has been attached to aid in making things clear. There are other columns in the original sheet which do not apply to this question, so they are omitted.


Thank you very much


SUMIF - OR.xlsx

[Kaper]

Try:

Please Login or Register  to view this content.

[Flash_82]

Thank you Kaper!

At first it seemed to work, but when I checked it in my example, it leaves out product "C" in row 4, even though it has the same criteria as product "F" in row 7. Any thoughts on why it leaves out the values in row 4, or am I overlooking something?