Help with Pivot Tables

shimon

New Member
Joined
Jul 26, 2013
Messages
1
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
ABCDEFGHIJ
1COUNT QTY Storage SAP QTY Storage+/- DifferenceCOUNT QTY ProductionSAP QTY Production+/- Difference2COGI QtyDeficit QtyCOSTPPU
217,420.0017,420.000621874-2530-253-£1,096.584.33431
32,035.001,641.0039422622600394£423.831.0757
41,641.000.00164100001641£2,224.051.3553
50.004,380.00-43800190-1900-4570-£2,064.130.45167
64,380.000.004380190019004570£2,376.400.52
716,780.00780.001600010948109480016000£156.320.00977
Sheet4
Cell Formulas
RangeFormula
H2:H7H2=C2+F2+G2
I2:I7I2=IFERROR((H2*J2),"")
C2:C7,F2:F7C2=A2-B2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I2:I2638Cell Valuebetween 0 and -500textNO
I2:I2638Cell Valuebetween 0 and 500textNO
I2:I2638Cell Value>500textNO
I2:I2638Cell Value<-500textNO
A1:C1Other TypeColor scaleNO
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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
ABCDEFGHIJK
1COUNT QTY Storage SAP QTY Storage+/- DifferenceCOUNT QTY ProductionSAP QTY Production+/- Difference2COGI QtyDeficit QtyCOSTPPUFavourable / Unfavourable
217420174200621874-2530-253-1096.580434.33431Unfavourable
32035164139422622600394423.82581.0757Favourable
4164101641000016412224.04731.3553Favourable
504380-43800190-1900-4570-2064.13190.45167Unfavourable
64380043801900190045702376.40.52Favourable
7167807801600010948109480016000156.320.00977Favourable
8
9
10
11
12Favourable / Unfavourable
13FavourableUnfavourableGrand Total
14Sum of COST5,181( 3,161)2,020
15
Sheet1
Cell Formulas
RangeFormula
H2:H7H2=C2+F2+G2
I2:I7I2=IFERROR((H2*J2),"")
C2:C7,F2:F7C2=A2-B2
K2:K7K2=IF(I2<0,"Unfavourable","Favourable")
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top