sum salesvalue if not in list

[omel]

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

 

[Special-K99]

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]

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)

 

[Aladin Akyurek]

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]

Thanks a lot Aladin Akyurek it works great

Best regards

Omel

 

[Aladin Akyurek]

Thanks a lot Aladin Akyurek it works great

Best regards

Omel

You are welcome.

 

[omel]

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

 

[Aladin Akyurek]

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]

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 ??

 

[Aladin Akyurek]

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.