So I am trying to draft a formula that looks at a range of transactions (Col A & B) and sums all values (Cal B) where the description (Cal A) is NOT equal to a side set of names that are located in another range. I am able to do this where the not equal to is a single cell or value, but when I change it to a range it seems to add MORE than the total sum of all lines. Seem screen cap below:
Untitled.jpg
If I want to sum all the values that ARE EQUAL to the side range, I used this: =SUMPRODUCT(SUMIF(A4:A12,D6:D7,B4:B12)) in Cell D5 and got 25, which is correct for all values tagged cats + all values tagged dogs.
If I want to sum all the values that are NOT EQUAL to just "cats" (Cell D6), I used this: =SUMIF(A4:A12,"<>"&D6,B4:B12) in Cell D11 and got 44, which is also correct for all values that are NOT tagged cats.
However, when I try to combine the two and sum all values that are NOT EQUAL to "cats" or NOT EQUAL to "dogs", I used this: =SUMPRODUCT(SUMIF(A4:A12,"<>"&D6:D7,B4:B12)). The result I'm looking for should be 30 (total sum of 55 less 11 for lines tagged "cats" and 14 for lines tagged "dogs"), however I am getting 85, which is somehow more than the total sum of the list.
Is this possible with these formulas and I have a syntax error somewhere, or do I have to do a series of SUMIF(range, "cats",sumrange) + SUMIF(range, "dogs", sumrange)? For this example that is no issues, but I'm looking to have a LONG list of NOT EQUAL to values and just referencing that range of excluded values would be much cleaner.
Thoughts?
Thanks,
Bookmarks