Help with Sum if formula

[ashl]

Hi,


I'm trying to set up a formula that ultimately gives me the cost of return for a product plus sales total of a product including returns. I want the formula to ignore the line where the return $ amount is so that I can track what the total would of been against the actual total sold.


So the formual goes like this:


when(retuns="yes";when(Product=Shoes;sum(Returns_amount;


this is the bit where i need help I iwant it to sum the returns amount and totals amount for that product.


If returns="no", I want it to ignore the returns amount line and give me the sum of the sales amount for that line.


I'm stumped Ive tried so many variations that ive confused myself

 

[Hui]

Ashl


Can you post a sample file, Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook

so we can see the layout of your data

 

[Luke M]

Here's the layout I'm assuming is in A1:D6

[pre]

Returns	Product	Returns	Sales
yes	jacket	1	1
yes	jacket	2	1
yes	shoes	3	1
No	shoes	4	1
no	club	5	1

[/pre]
Formula would be:

=SUMPRODUCT((B2:B6="shoes")*(D2:D6+((A2:A6="yes")*(C2:C6))))

which evaluates to 5.

If I understood correctly, the result of this formula is the 3+1 in row 4, and the 1 row 5.


If this is not correct, can you expand on this example and show what result you are wanting?

 

[ashl]

Hi guys, I dont think I explained myself very well I have friday brain at the moment. Hope this clarifies


https://www.box.com/s/d8ngveyq0d1nizw0q8yz

 

[ashl]

Say if 100 was changed to 130 as the sales price in teh second table I would want to get for shoes the value 3 because all together there was a total of 6 sales but 3 returns.

 

[Hui]

In B19:

=SUMPRODUCT(($B$2:$B$6=$A19)*($E$2:$E$6=B$18)*($D$2:$D$6-$C$2:$C$6))

Copy across and down

 

[ashl]

Perfect thank u