How to Sum based on criteria

[BlastRanger]

Hi,
Look This example :
-------A---------------B
blastranger------100
rangerblade-----200
blastranger------200
Monkey----------300
Rabbit------------400
rangerblade-----500
Monkey----------100
Rabbit-------------200

I want to sum the Value of blastranger and Rabbit which will return result :
900

I use SUMIFS(B1:B8,A1:A8,"blastranger",A1:A8,"Rabbit")
the result is 0

I wonder why it is 0, how to sum using SUMIFS, or do I make some mistake here?

[DonkeyOte]

SUMIFS is an AND based test - a single SUMIF(S) can not perform an OR based calculation.

In this instance:

=SUM(SUMIF(A1:A8,{"blastranger","Rabbit"},B1:B8))

[canapone]

Hi, maybe

=SUMPRODUCT((A1:A8={"blastranger,"Rabbit"})*B1:B8)

Regards

[BlastRanger]

SUMIFS is an AND based test - a single SUMIF(S) can not perform an OR based calculation.

Then why if I use following formula using sumifs it will perform an OR based calculation :
Look This example :
-------A---------------B
blastranger------100
rangerblade-----200
blastranger------200
Monkey----------300
Rabbit------------400
rangerblade-----500
Monkey----------100
Rabbit-------------200

=SUMIFS(B1:B8,A1:A8,"<>rangerblade",A1:A8,"<>Monkey")
This Will sum value of blastranger and Rabbit and return 900

That mean Sum value other than rangerblade AND other than Monkey

SUMIFS(B1:B8,A1:A8,"blastranger",A1:A8,"Rabbit")
This mean Sum value of blastranger AND value of Rabbit

I think it's not an OR based calculation here
????

[DonkeyOte]

Then why if I use following formula using sumifs it will perform an OR based calculation ...

=SUMIFS(B1:B8,A1:A8,"<>rangerblade",A1:A8,"<>Monkey")
:

the above (as you state yourself) is not an OR test - it is an AND test

the formula states sum values in B where value in A is neither "rangerblade" nor "monkey"

The below:

Please Login or Register  to view this content.

says sum B where value in A is both "blastrange" and "rabbit" - a cell can not contain two different values simultaneously - hence 0

[BlastRanger]

OK that does make the sense