Subtract Sumproduct from a sumproduct

I have a situation where I need to collect all the information for a certain criteria, but then I need to subtract a certain combination of instance from that formula.

I am including a simplified version of what I am looking for, as the idea of explaining the actual data gives me a bit of a headache. So, although this example doesn't exactly make sense, it is essentially what I am trying to do.

So, my example has column A with animal names, Column B has colors, column C the item in question, column D the quantity.

Here is the formula I am trying to get to work, however it is only answering the first half of the equation.

=SUMPRODUCT((A2:A14="DOG")+(A2:A14="CAT"),--(B2:B14="BLUE")+
B2:B14="yellow"),D2:D14)-SUMPRODUCT((A2:A14="CAT"),--(B2:B14="yellow"),D2:D14)


So, basically, I want every instance where A equals Dog and Cat, and B equals blue and yellow, but then I want to subtract where the combination is Cat and yellow. Doesn't seem to matter what I do though, it is not subtracting.

Please help!

Try:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Instead of subtracting a subset, try only including what you want:

Please Login or Register  to view this content.

Is that something you can work with?

That's my second one..

Then perhaps you can add a column that concatenates columns A and B

e.g. =A2&"_"&B2 copied down

then you create a list of acceptable combinations separately.. (put an underscore between the 2 words) e.g. dog_blue, dog_yellow, cat_blue

then use a formula like:

=SUMPRODUCT(--(ISNUMBER(MATCH(E2:E14,N2:N10,0))),D2:D14)

where E2:E14 contains the concatenated helper column, N2:N10 contains the list of combined acceptable strings.