# weighted sumif not working as expected

#### Mr2017

##### Well-known Member
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?

 Department Product Base Sales Promo Sales Uplift On Deal? Weighted PROMO sales Weighted UPLIFT sales Food Chocolate 2000 3000 1000 On Deal 75% 111% Food Chicken 0 0 0 On Deal 0% 0% Food Cheese 500 300 -200 On Deal 8% Food Milk 600 700 100 On Deal 18% 11% GM Sunglasses 500 500 0 Not On Deal GM Skateboards 500 500 0 Not On Deal

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Maybe? Your SUMIF is including the -200 in cell E4. Try using a SUMIFS function as below.
Excel Workbook
ABCDEFGH
1DepartmentProductBase SalesPromo SalesUpliftOn Deal?Weighted PROMO salesWeighted UPLIFT sales
2FoodChocolate200030001000On Deal0.7590.91%
3FoodChicken000On Deal00.00%
4FoodCheese500300-200On Deal0.075
5FoodMilk600700100On Deal0.1759.09%
6GMSunglasses5005000Not On Deal
7GMSkateboards5005000Not On Deal
Sheet

Replies
5
Views
202
Replies
6
Views
377
Replies
4
Views
4K
Replies
4
Views
793
Replies
5
Views
532

1,203,483
Messages
6,055,663
Members
444,806
Latest member
tofanexcel

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