Search code examples
excel-formulasumifs

SUMIFS excluding two criteria

I'm trying to do SUMIFS with two criteria I want to exclude. I want to count the amount of fruit sold by all, excluding cherries sold by James.

A B C
Qty Product Salesperson
5 Apples James
10 Apples Jack
15 Apples Ben
20 Bananas Ben
15 Bananas Jack
10 Cherries James
5 Grapes Ben
10 Grapes James
15 Cherries Jack
20 Melons Ben

I've tried

=SUMIFS(A:A,B:B,AND(B:B"<>Cherries",C:C"<> James"))

but got an error.

=SUMIFS(A:A,B:B,"<>Cherries",C:C,"<>James")

Also does not work as it doesn't count and Cherries or anything sold by James.

From the data above I would expect 115 as my answer.

Solution

  • subtract the part from the whole:

    =SUM(A:A)-SUMIFS(A:A,B:B,"Cherries",C:C,"James")

    enter image description here