Search code examples
excelfiltersumor-operator

Sum with multiple filters excel with or-operator?

Hi I would like to be able to sum in an excel using multiple filters and connecting them also be "OR". Let's say I have this List:

See image

to sum all the prices that belong to category "a" I used the following formula.

{=SUM((KAT_1="a")*(PREIS))}

For all prices that belong to "open" likewise

{=SUM((KAT_2="open")*(PREIS))}

If I want to sum only those prices that belong to "a" and "open" I used

{=SUM((KAT_1="a")*(KAT_2="open") *(PREIS))}

Now my problem. If I want to sum everything that belongs to "a" or "open" I could try

{=SUM(((KAT_1="a")+(KAT_2="open"))*(PREIS))}

But then it says 47. Which double counts those that are both "open" and "a".

How can I prevent this from happening?

One solution would be:

{=SUM((((KAT_1="a")+(KAT_2="open"))-((KAT_1="a")(KAT_2="open")))(PREIS))}

But isn't there an easier way? Especially if you had a more complex list this ends up being very complicated.

Hope you understand my question!

Thanks!

Solution

  • for the OR case you can:

    {=SUM(IF(((KAT_1="a")+(KAT_2="open")>0),PREIS,""))}

    (Replace + with * for the AND case, then you can have an elegant parallel construction)