Search code examples
excelexcel-2013sumifs

Getting #VALUE error in SUMIFS

I have this simple table:

    A       B
1   Fruits  Amount
2   Apple   2
3   Orange  3
4   Banana  5

I do:

=SUMIFS(B2:B4,A2:A4,"Apple", A2:A4, "Orange")

It returns 0. If I erase either A3:A5, "Apple" or A3:A5, "Orange", it works. What's wrong?

Solution

  • SUMIFS() uses AND not OR so it is looking for one cell that is both Apple and Orange.

    Use this instead:

    =SUM(SUMIFS(B:B,A:A,{"Apple","Orange"}))