Sumproduct, external range lookup

[umplebyuk]

Good morning,

I have a list of sales with categories, is there a way I can use sumproduct to sum sales based on an external list of categories however some categories I want to add in and some I want to deduct. The below fall under a category for example of Net Fruit;

Apples +1,
Pears +1,
Orange -1

Result would be Apple + Pear - Orange

If am currently using SUM(SUMIFS(Sales_TY[Period1],Sales_TY[Sales Category],IF(Table_Lookups_PandL_Wording[P&L Wording]=$A86,Table_Lookups_PandL_Wording[Sales Category]))) as an array formula which works to sum all but does not factor in the -1 Orange element, it just sums it (Where $A86 = "Net Fruit")

Thank yuo in advance,
James

 

[jasonb75]

If you want to deduct then you need 2 parts to the formula, something like

=SUM(SUMIFS(sum range, criteria range, + criteria))-SUM(SUMIFS(sum range, criteria range, - criteria))

Having said that, your question is of the type that is open to misinterpretation without a visual representation of the data (XL2BB please, not screen captures)