1

What I'm trying to do is, I have on the sheet of multiple dates (report dates) in column A and a transition date column in column E. From B - D I have numbers in each column corresponding with actual price then fees. What happens in this is sheet is reports come in and get transitioned on a specific date and switches fees from Column C (Before Transition Date) to Column D (After Transition Date). What I want to do with the Pivot table is this: I want to be able to add column B & C but when the item hits the transition date to switch over to adding B & D and continues the SUM of it all. My thought process is it would have to do a IF statement, something along the lines of 

IF(Transition Date >= Date, Add B & C, IF(Transition Date <= Date, Add B & D),0)

But I could be wrong, just trying to figure this part out on a Pivot Table. Again I am new with Pivot Tables so if my terminology is incorrect please correct me.

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
Maykid
  • 497
  • 3
  • 7
  • 17
  • Image of your data with headers and desired outcome would help – QHarr Nov 06 '17 at 19:37
  • =IF(A2 – QHarr Nov 06 '17 at 19:47
  • [link](https://imgur.com/a/4TicU) This is what a sample of it looks like. The numbers do vary from column to column. Hopefully this helps! – Maykid Nov 06 '17 at 20:29
  • Hi, Edit link into question so others can find it easily. I have given a non-pivottable option above in comments (you would drag this formula down) and a pivottable option below as answer. Try those. – QHarr Nov 06 '17 at 20:31

2 Answers2

1

If i have understood you correctly you want to add a calculated field to your pivot.

Say your data looked like this Range("A1:E4") in image:

Image of data and pivot

You would add a calculated field to the pivot (Starting row 13 in image) using the following formula:

=IF(Date < TransitionDate, Cost + 'Ore Fee', Cost + 'Mineral Fee')

This equates to:

=IF(ReportDate < TransitionDate, ColB + BeforeTransitionDate, ColB + AfterTransitionDate)

Your mileage on layout may vary as i am using an old mac which is not ideal for pivottables and i have hidden some irrelevant pivot fields.

Windows machine i think you add a calculated field by selecting a cell inside the pivot, the going to Analyze -> Fields, Items, & Sets -> click the little down arrow -> Calculated Field. Example here

Layout of pivot:

Approximate pivottable layout (using Mac)

Where to enter calculated field:

Calculated field

And comparing with in sheet formulas without pivottable see Column H rows 14:16 (formula i gave from the comments):

Comparing pivot with in sheet formulas

QHarr
  • 83,427
  • 12
  • 54
  • 101
0

There seems to be no need for a PivotTable here, as you can create an additional column (say, ActualPrice) that conditions on summing columbs B & D or C & D:

enter image description here

In the above example, TransactionDate and TransitionDate were made with =DATE(2017,12,randbetween(1,31)), with conditional formatting highlighting the TransactionDates that are on-or-after/before TransitionDate in red/green. The ActualPrice is calculated as =IF(A2<E2,B2,C2)+D2.

If you want you can create a PivotTable to aggregate all of this, or just return the total sum in some cell (outside Column F) as =SUM(F:F).

Werner
  • 14,324
  • 7
  • 55
  • 77