Sum If a Pivot Table Data Set Based on Date

[bluelabel]

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

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

 

[RasGhul]

It might be easier to SUMIFS on your data?

Book1
	A	B	C	D	E
1	Sum of DUE	Column Labels
2	Week Ending	AP		Row Labels	Sum of AP
3	01-03-19			01-Mar
4	10-05-19	2,551		10-May	2551
5	17-05-19	3,526		17-May	3526
6	24-05-19	11,866		24-May	11866
7	31-05-19	54,079		31-May	54079
8	07-06-19	13,805		07-Jun	13805
9	14-06-19	8,223		14-Jun	8223
10	21-06-19	5,417		21-Jun	5417
11
12	Less than	21-06-19
13
14	sum	94,050
Sheet1

Cell Formulas
Range	Formula
B14	=SUMIFS($B$3:$B$10,$A$3:$A$10,"<"&B12)

 

[bluelabel]

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.

 

[RasGhul]

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.

 

[bluelabel]

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.