weighted sumif not working as expected

[Mr2017]

Hi

I've got a formula that's not working, as expected.

Please find below a very simple sample of the data and formulae I've used:

If you copy and paste the data below, it should go from cell A1:H7.

I'd like to use a sumif to do two things

1) get the weight of the "Promo sales" for each product based on the "Department" they're in ie Food or GM AND whether they're on Deal, which I've done correctly using this formula (this was in cell G2 then dragged down): =IF(F2="Not on Deal","",D2/SUMIF(A:A,A2,D:D))

2) get the weight of the "Uplift" for a product in the "Food" department, but only for products that are "On Deal" in column H. I used this formula (in cell H2 and dragged it down), but it didn't work!? =IF(F2="Not on Deal","",IF(E2<0,"",E2/SUMIF(A:A,A2,E:E))) Does anyone know how I should amend it, please?


[TABLE="width: 634"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 634"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Department[/TD]
[TD]Product[/TD]
[TD]Base Sales[/TD]
[TD]Promo Sales[/TD]
[TD]Uplift[/TD]
[TD]On Deal? [/TD]
[TD]Weighted PROMO sales[/TD]
[TD]Weighted UPLIFT sales[/TD]
[/TR]
[TR]
[TD]Food[/TD]
[TD]Chocolate[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]1000[/TD]
[TD]On Deal[/TD]
[TD="align: right"]75%[/TD]
[TD="align: right"]111%[/TD]
[/TR]
[TR]
[TD]Food[/TD]
[TD]Chicken[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]On Deal[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]0%[/TD]
[/TR]
[TR]
[TD]Food[/TD]
[TD]Cheese[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]-200[/TD]
[TD]On Deal[/TD]
[TD="align: right"]8%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Food[/TD]
[TD]Milk[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]700[/TD]
[TD="align: right"]100[/TD]
[TD]On Deal[/TD]
[TD="align: right"]18%[/TD]
[TD="align: right"]11%[/TD]
[/TR]
[TR]
[TD]GM[/TD]
[TD]Sunglasses[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]0[/TD]
[TD]Not On Deal[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]GM[/TD]
[TD]Skateboards[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]0[/TD]
[TD]Not On Deal[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[AhoyNC]

Maybe? Your SUMIF is including the -200 in cell E4. Try using a SUMIFS function as below.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Department	Product	Base Sales	Promo Sales	Uplift	On Deal?	Weighted PROMO sales	Weighted UPLIFT sales
2	Food	Chocolate	2000	3000	1000	On Deal	0.75	90.91%
3	Food	Chicken	0	0	0	On Deal	0	0.00%
4	Food	Cheese	500	300	-200	On Deal	0.075
5	Food	Milk	600	700	100	On Deal	0.175	9.09%
6	GM	Sunglasses	500	500	0	Not On Deal
7	GM	Skateboards	500	500	0	Not On Deal
Sheet