Excel SUMIFS() Statement

Question

I am looking to create a function that will use either SUMIFS() or something else that would acomplish the following on worksheet2 (w2):

A    B  C
RED  5  CAT
RED  7  CAT
BLUE 8  DOG
RED  2  DOG

such that all the values containing both CAT and RED are summed together, excluding all other values, but between worksheets.

So worksheet one would display all of these combinations. What I have right now is =sumifs(w2!b2:b4,w2!a2:a4,red,w2!c2:c4,cat) the function works on the same worksheet, but not across. Even with indexing the issue, =index(w2!a2:c4,sumifs(w2!b2:b4,w2!a2:a4,red,w2!c2:c4,cat)) I cannot get a value.

Answer 1

Consider:

=SUMPRODUCT(--(C1:C4="CAT")*(A1:A4="RED")*(B1:B4))

If you have more than one worksheet, then make a sum for each sheet and then add them up.