Help with Pivot Tables

[shimon]

Hello, I'm wondering if anyone can help me, I'm not a complete novice with Excel, but I've never really had to use Pivot tables until now.
I have a column named Cost in a sheet that calculates the £ value of a stock write off / write on, I would like to use a pivot table to separate the total written off per month and the total written on per month for reports.
I have tried setting up a pivot table but using Sum of Cost in the Values Area is deducting the negative write off's and then giving me the total.
How do I separate these to then get a total for each?

Any help would be great.

CycleCountRecording_2022.xlsb.xlsx
	A	B	C	D	E	F	G	H	I	J
1	COUNT QTY Storage	SAP QTY Storage	+/- Difference	COUNT QTY Production	SAP QTY Production	+/- Difference2	COGI Qty	Deficit Qty	COST	PPU
2	17,420.00	17,420.00	0	621	874	-253	0	-253	-£1,096.58	4.33431
3	2,035.00	1,641.00	394	226	226	0	0	394	£423.83	1.0757
4	1,641.00	0.00	1641	0	0	0	0	1641	£2,224.05	1.3553
5	0.00	4,380.00	-4380	0	190	-190	0	-4570	-£2,064.13	0.45167
6	4,380.00	0.00	4380	190	0	190	0	4570	£2,376.40	0.52
7	16,780.00	780.00	16000	10948	10948	0	0	16000	£156.32	0.00977
Sheet4

Cell Formulas
Range	Formula
H2:H7	H2	=C2+F2+G2
I2:I7	I2	=IFERROR((H2*J2),"")
C2:C7,F2:F7	C2	=A2-B2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I2:I2638	Cell Value	between 0 and -500	text	NO
I2:I2638	Cell Value	between 0 and 500	text	NO
I2:I2638	Cell Value	>500	text	NO
I2:I2638	Cell Value	<-500	text	NO
A1:C1	Other Type	Color scale		NO

 

[Alex Blakenburg]

What you are trying to do is similar to grouping data. For the standard pivot this is easiest to do by using helper columns.

Book3
	A	B	C	D	E	F	G	H	I	J	K
1	COUNT QTY Storage	SAP QTY Storage	+/- Difference	COUNT QTY Production	SAP QTY Production	+/- Difference2	COGI Qty	Deficit Qty	COST	PPU	Favourable / Unfavourable
2	17420	17420	0	621	874	-253	0	-253	-1096.58043	4.33431	Unfavourable
3	2035	1641	394	226	226	0	0	394	423.8258	1.0757	Favourable
4	1641	0	1641	0	0	0	0	1641	2224.0473	1.3553	Favourable
5	0	4380	-4380	0	190	-190	0	-4570	-2064.1319	0.45167	Unfavourable
6	4380	0	4380	190	0	190	0	4570	2376.4	0.52	Favourable
7	16780	780	16000	10948	10948	0	0	16000	156.32	0.00977	Favourable
8
9
10
11
12									Favourable / Unfavourable
13									Favourable	Unfavourable	Grand Total
14								Sum of COST	5,181	( 3,161)	2,020
15
Sheet1

Cell Formulas
Range	Formula
H2:H7	H2	=C2+F2+G2
I2:I7	I2	=IFERROR((H2*J2),"")
C2:C7,F2:F7	C2	=A2-B2
K2:K7	K2	=IF(I2<0,"Unfavourable","Favourable")