Excel Pivot Table IF Statement

Question

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.

Image of your data with headers and desired outcome would help — QHarr
– QHarr, Commented Nov 6, 2017 at 19:37
link This is what a sample of it looks like. The numbers do vary from column to column. Hopefully this helps! — Maykid
– Maykid, Commented Nov 6, 2017 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
– QHarr, Commented Nov 6, 2017 at 20:31

QHarr · Accepted Answer · 2017-11-06 20:55:59Z

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:

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:

Where to enter calculated field:

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

Werner · Accepted Answer · 2017-11-06 20:26:02Z

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:

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).

answered Nov 6, 2017 at 20:26

Werner

15.3k8 gold badges58 silver badges82 bronze badges

Collectives™ on Stack Overflow

Excel Pivot Table IF Statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related