# sum salesvalue if not in list

#### omel

##### New Member
Hi

I have 2 list one with 10 Items listed and one list in another workbook with 100 of items and salesvalue for those, now i would like to sum all of the salesvalue of items not in the first list how to do that ???

I use excel 2010

Best regards

Omel

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

#### Special-K99

##### Well-known Member
Try this
In my test col A is your 10 item list (ive only used 6 items)
col D is the items
col E is the salesvalue

enter this formula in B1
=IF(ISNA(VLOOKUP(A1,D\$1:E\$10,2,FALSE)),0,VLOOKUP(A1,D\$1:E\$10,2,FALSE))
and copy down

in another cell
=SUM(E1:E10)-SUM(B1:B6)
gives you the figure you want
That is total of all items subtract item prices found in the list gives you the total salesvalue of all items not in the list

You'll have to change the D1:E10 to the 100 item & salesvalue range

#### omel

##### New Member
Hi

Thx for your reply, is it not possible to build a solution where its only one function like Sum(Item not in list sum value)

##### MrExcel MVP
Hi

I have 2 list one with 10 Items listed and one list in another workbook with 100 of items and salesvalue for those, now i would like to sum all of the salesvalue of items not in the first list how to do that ???

I use excel 2010

Best regards

Omel

=SUM(E2:E100)-SUMPRODUCT(SUMIF(D2:D100,A2:A11,E2:E100))

where A2:A11 houses the items to exclude.

#### omel

##### New Member
Thanks a lot Aladin Akyurek it works great

Best regards

Omel

##### MrExcel MVP
Thanks a lot Aladin Akyurek it works great

Best regards

Omel

You are welcome.

#### omel

##### New Member
Is it possible to convert the sumif to a Sumproduct function so it works with a closed workbook ???

Last edited:

##### MrExcel MVP
Is it possible to convert the sumif to a Sumproduct function so it works with a closed workbook ???

Try...

=SUM(E2:E100)-SUMPRODUCT(ISNUMBER(MATCH(D2:D100,A2:A11,0))+0,E2:E100)

#### omel

##### New Member
Great. i convertet the first sum to sumproduct too just in case , and all works perfectly i did not use the "+0" what is that for ??

##### MrExcel MVP
Great. i convertet the first sum to sumproduct too just in case , and all works perfectly i did not use the "+0" what is that for ??

You need to implement the formula the way I suggested:

=SUM(E2:E100)-SUMPRODUCT(ISNUMBER(MATCH(D2:D100,A2:A11,0))+0,E2:E100)

+0 converts TRUE/FALSE evaluations to 1/0 evaluations, i.e., numbers SumProduct requires. SUM works with both open and closed files just fine.

Replies
1
Views
58
Replies
31
Views
358
Replies
3
Views
146
Replies
5
Views
129
Replies
7
Views
106

1,195,943
Messages
6,012,431
Members
441,700
Latest member
Warbo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back