Pivot Table to Calculate only Positive numbers & dynamic column selection

T

tinferns

Board Regular
Joined
Aug 18, 2009
Messages
150
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hello All.. request you to help me out on the below:

Base data
DateDetailsProduct No.Amount
1-Jan-20Purchase XX grams18456
1-Feb-20Purchase XX grams26684
22-Jan-20Purchase XX grams63264
23-Jan-20Purchase XX grams122630
24-Jan-20Purchase XX grams99951
15-Mar-20Purchase XX grams116687
22-Apr-20Purchase XX grams15623
1-May-20Purchase XX grams35548
4-May-20Sold product1-25000
27-Jun-20Purchase XX grams44565
11-Aug-20Purchase XX grams25585
26-Aug-20Purchase XX grams14468
29-Aug-20Purchase XX grams35960
30-Aug-20Purchase XX grams75443
2-Sep-20Purchase XX grams28765
4-Sep-20Purchase XX grams114568
9-Sep-20Purchase XX grams14495
11-Sep-20Purchase XX grams57843
13-Sep-20Sold product3-50000
15-Sep-20Purchase XX grams35689

Expected Pivot for the above data:
Sum of AmountProduct No.
Months1, 2 & 3Other Products
Jan845615845
Feb6684
Mar6687
Apr5623
May5548
Jun4565
Aug160135443
Sep1894912411
Grand Total6127344951

What's different in this report:
1) I have manually calculated and removed the earnings. I have calculated only POSITIVE values and ignored all Negative values
2) I have combined calculations of Product No 1 - 3 and combined calculations for the remaining products

Can the above 2 calculations be made possible using Pivot. I know its very much possbile using SUMPRODUCT. However, I prefer Pivot.

Please help.

Thanks
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
Using the amount in a filter would be cumbersome when data changed. Easiest solution I See. Add a column using sign (amount). Put that in the filter.
Second you can select product 1,2,3 and group them, right click on the pivot.
 
Upvote 0
GraH said:
Using the amount in a filter would be cumbersome when data changed. Easiest solution I See. Add a column using sign (amount). Put that in the filter.
Second you can select product 1,2,3 and group them, right click on the pivot.
Click to expand...
Thanks G. Grouping solves one issue. Any idea how to get POSITIVE numbers only ?
 
Upvote 0
I meant like this, using sign function as extra column in your data.
Book1
ABCDEFGHIJKL
1DateDetailsProduct No.AmountSignSign1
21/01/2020Purchase XX grams184561
31/02/2020Purchase XX grams266841Sum of AmountColumn Labels
422/01/2020Purchase XX grams632641Row LabelsGroup1Group2Grand Total
523/01/2020Purchase XX grams1226301jan84561584524301
624/01/2020Purchase XX grams999511feb66846684
715/03/2020Purchase XX grams1166871mrt66876687
822/04/2020Purchase XX grams156231apr56235623
91/05/2020Purchase XX grams355481mei55485548
104/05/2020Sold product1-25000-1jun45654565
1127/06/2020Purchase XX grams445651aug16013544321456
1211/08/2020Purchase XX grams255851sep189491241131360
1326/08/2020Purchase XX grams144681Grand Total6127344951106224
1429/08/2020Purchase XX grams359601
1530/08/2020Purchase XX grams754431
162/09/2020Purchase XX grams287651
174/09/2020Purchase XX grams1145681
189/09/2020Purchase XX grams144951
1911/09/2020Purchase XX grams578431
2013/09/2020Sold product3-50000-1
2115/09/2020Purchase XX grams356891
Sheet1
Cell Formulas
RangeFormula
E2:E21E2=SIGN((D2))
 
Upvote 0
GraH said:
I meant like this, using sign function as extra column in your data.
Book1
ABCDEFGHIJKL
1DateDetailsProduct No.AmountSignSign1
21/01/2020Purchase XX grams184561
31/02/2020Purchase XX grams266841Sum of AmountColumn Labels
422/01/2020Purchase XX grams632641Row LabelsGroup1Group2Grand Total
523/01/2020Purchase XX grams1226301jan84561584524301
624/01/2020Purchase XX grams999511feb66846684
715/03/2020Purchase XX grams1166871mrt66876687
822/04/2020Purchase XX grams156231apr56235623
91/05/2020Purchase XX grams355481mei55485548
104/05/2020Sold product1-25000-1jun45654565
1127/06/2020Purchase XX grams445651aug16013544321456
1211/08/2020Purchase XX grams255851sep189491241131360
1326/08/2020Purchase XX grams144681Grand Total6127344951106224
1429/08/2020Purchase XX grams359601
1530/08/2020Purchase XX grams754431
162/09/2020Purchase XX grams287651
174/09/2020Purchase XX grams1145681
189/09/2020Purchase XX grams144951
1911/09/2020Purchase XX grams578431
2013/09/2020Sold product3-50000-1
2115/09/2020Purchase XX grams356891
Sheet1
Cell Formulas
RangeFormula
E2:E21E2=SIGN((D2))
Click to expand...

That was brilliant G. Thanks .. it worked. Cheers..
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
15
Views
1K
Wishmaster40
W
D
  • Question
Move data from one sheet to mutlipe sheets based on sheet name
Replies
3
Views
492
Herakles
H
D
  • Question
Using VBA/Macros to Duplicate Sheets and Inserting Individual Rows from Table
Replies
2
Views
610
donnington
D
T
  • Solved
Conditional field in pivot table
Replies
5
Views
524
James006
James006
S
  • Question
Streamlining a long, manual formula
Replies
1
Views
741
SewStage
S

Forum statistics

Threads
1,214,429
Messages
6,119,433
Members
448,897
Latest member
ksjohnson1970

Share this page

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