# Sum based on whether multiple cells don't contain multiple values

1. ## Sum based on whether multiple cells don't contain multiple values

So I can't wrap my head around this one. I'm trying to get the total of all cells on Sheet 1 Column B that do not equal the values on sheet 2 (D2:D5) whether positive or negative that are labeled as Non-Union in Sheet 1 Column A to populate in cell E5 on Sheet2.

I know this sounds pretty confusing but I've attached a sample spreadsheet that I hope makes it easier to understand. Any help on this is much appreciated.

2. ## Re: Sum based on whether multiple cells don't contain multiple values

Try:

=SUMPRODUCT(--(Sheet1!\$A\$2:\$A\$190="Non-Union"),--ISNA(MATCH(Sheet1!\$B\$2:\$B\$190,Sheet2!\$D\$2:\$D\$4,0)),Sheet1!\$B\$2:\$B\$190)

3. ## Re: Sum based on whether multiple cells don't contain multiple values

This is great but it seems to be including -6.00 and -18.00 in its calculations?

EDIT: Nevermind I figured it out. I added a second ISNA string for the negative values but I would never have been able to do it without your intitial formula Thanks!!!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1