Account for returns resulting in negative quantity with positive sales

[Caly]

Hi I have a report with data that contains orders with sales and returns. For one order there were three lines where two weee returns and one was a sale. When doing a pivot the amount of the units purchased results in a negative but the sales is positive. I can’t have the negative quantity with positive sales but is there a way to capture this with a formula?

Example below
Order 1: Quantity = -1, sales = -0.58
Order 2: Quantity = 1, sales = 35.00
Order 3: Quantity = -1, sales = -2.58

The sum total Quantity results in -1 and sales results in 31.84

But is there a way with a formula or even the pivot table to make this scenario show positive for the quantity?

 

[jtakw]

Hi,

Do you mean something like this:

Book3.xlsx
	A	B	C
3		Quantity	Sales
4	Order 1	-1	-0.58
5	Order 2	1	35
6	Order 3	-1	-2.58
7
8	Total	1	31.84
9		1
Sheet937

Cell Formulas
Range	Formula
C8	C8	=SUM(C4:C6)
B8	B8	=ABS(SUM(B4:B6))
B9	B9	=IF(COUNTIF(B4:B6,">0"),ABS(SUM(B4:B6)),SUM(B4:B6))

Use B9 formula for in case you want to show -3 when All orders were returned.

 

[Caly]

Wow thank you so much this is perfect. Curious, is there a way to do this in a pivot table?

 

[jtakw]

You're welcome, glad it works for you.
I don't use pivot tables much, but I would think the formula should work with it.