Sum If a Pivot Table Data Set Based on Date

bluelabel

Board Regular
Joined
Nov 27, 2008
Messages
76
Hi Gurus,

Am I able to sumif a range of dates within a pivot table.

The formula I have is as below, but returns a #ref .

=GETPIVOTDATA("DUE",$A$3,"Group","AP","Payment Date","<"&DATE(2019, 6, 21))


Code:
 [TABLE]
<tbody>[TR]
[TD]Sum of DUE[/TD]
[TD="width: 114"]Column   Labels[/TD]
[TD="width: 63"][/TD]
[/TR]
[TR]
[TD]Week   Ending[/TD]
[TD="class: xl201"]AP[/TD]
[TD="class: xl201"]CX[/TD]
[/TR]
[TR]
[TD="class: xl200"]1/03/2019[/TD]
[TD="class: xl199"][/TD]
[TD="class: xl199"][/TD]
[/TR]
[TR]
[TD="class: xl200"]10/05/2019[/TD]
[TD="class: xl199"]                        2,551[/TD]
[TD="class: xl199"][/TD]
[/TR]
[TR]
[TD="class: xl200"]17/05/2019[/TD]
[TD="class: xl199"]                        3,526[/TD]
[TD="class: xl199"][/TD]
[/TR]
[TR]
[TD="class: xl200"]24/05/2019[/TD]
[TD="class: xl199"]                      11,866[/TD]
[TD="class: xl199"][/TD]
[/TR]
[TR]
[TD="class: xl200"]31/05/2019[/TD]
[TD="class: xl199"]                      54,079[/TD]
[TD="class: xl199"][/TD]
[/TR]
[TR]
[TD="class: xl200"]7/06/2019[/TD]
[TD="class: xl199"]                      13,805[/TD]
[TD="class: xl199"][/TD]
[/TR]
[TR]
[TD="class: xl200"]14/06/2019[/TD]
[TD="class: xl199"]                        8,223[/TD]
[TD="class: xl199"][/TD]
[/TR]
[TR]
[TD="class: xl200"]21/06/2019[/TD]
[TD="class: xl199"]                        5,417[/TD]
[TD="class: xl199"]       1,312[/TD]
[/TR]
</tbody>[/TABLE]

Is there a way to have a formula be a sum of less than 21/06/2019, on the group AP when referencing the pivot table.
 
Last edited:

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
It might be easier to SUMIFS on your data?



Book1
ABCDE
1Sum of DUEColumn Labels
2Week EndingAPRow LabelsSum of AP
301-03-1901-Mar
410-05-192,55110-May2551
517-05-193,52617-May3526
624-05-1911,86624-May11866
731-05-1954,07931-May54079
807-06-1913,80507-Jun13805
914-06-198,22314-Jun8223
1021-06-195,41721-Jun5417
11
12Less than21-06-19
13
14sum94,050
Sheet1
Cell Formulas
RangeFormula
B14=SUMIFS($B$3:$B$10,$A$3:$A$10,"<"&B12)
 
Upvote 0
It might be easier to SUMIFS on your data?

Hi, Thank you for your response. The data is always dynamic. It might change a dozen times each day, at a minimum and regular basis, it is once a day.

The idea is to hit the refresh data button and have the new dates come into the table. My understanding is with the Sum if I will need to add the new dates and categories as they come into and out of the table.

The table is not always the same size too. The Categories come in and out depending on if there is data categorised to them.

So day one you might have three categories. CX AP, MG. Day two theres five categories CX, MG, CT, RM, WD, Day 3, two categories, CX, AP.
 
Upvote 0
If you convert your source data to a table we can use sumifs with table referencing to be dynamic;

e.g =sumifs(table1[AP],table1[Week Ending],"<"&B12)

Right click on your sheet tab for your source data and select view code this will open VBA box for your sheet
In the box that says (General), with the dropdown change to Worksheet

You should now see; and add this line of code
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
thisworkbook.RefreshAll
End Sub

As you update your source data the sheet will auto refresh your pivot each time you enter/change the sheet.
 
Last edited:
Upvote 0
thank you again.

I was trying to avoid directly referencing the data table with the sumifs (data is currently in a table) as the pivot convieniently summarises the data in a nice little easily readable format.

The pivot gives users the ability to drill down into the data if needed and further and organises for the end user. I'm trying to keep users away from raw data and the PT does this.
 
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