CODEWITHSUNDEEP
excel
T.Hannah
T.Hannah

Reputation: 111

Sumproduct conditional array exclusion

Please click for linked image

I'm trying to do a sumifs with an exclusion list, I put together a generic example to help explain.

In this example, I want to add all the animals except elephants and tigers. Easy to do with a sumproduct:

{=SUMPRODUCT(D5:D9*(C5:C9=TRANSPOSE(H5:H7)))}
=42

Problem is, with my particular file, it's much easier to handle and maintain an exclusion list. I have 80+ "animals" and I want to exclude around 8, but it's variable enough in specific exclusions and # of exclusions I would prefer to maintain an array, than hard code something in there.

I've tried:

{=SUMPRODUCT(D5:D9*(C5:C9<>TRANSPOSE(J5:J6)))}

{=SUMPRODUCT(D5:D9*NOT(C5:C9=TRANSPOSE(J5:J6)))}

But what I end up getting is Giraffes + Lions + Tigers + Rhinos (i.e. NOT Elephants) PLUS Giraffes + Elephants + Lions + Rhinos (i.e. NOT Tigers) = 116

Any ideas?

Upvotes: 0

Views: 200

Answers (1)

barry houdini
barry houdini

Reputation: 46361

Try using this approach

=SUMPRODUCT(D5:D9*ISNA(MATCH(C5:C9,J5:J6,0)))

You don't need "array entry" for that

If you want an "inclusion list" change ISNA to ISNUMBER

Upvotes: 1

Related Questions