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:

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
611
It might be easier to SUMIFS on your data?


<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Sum of DUE</td><td style=";">Column Labels</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Week Ending</td><td style=";">AP</td><td style="text-align: right;;"></td><td style=";">Row Labels</td><td style=";">Sum of AP</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">01-03-19</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">01-Mar</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">10-05-19</td><td style="text-align: right;;">2,551</td><td style="text-align: right;;"></td><td style=";">10-May</td><td style="text-align: right;;">2551</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">17-05-19</td><td style="text-align: right;;">3,526</td><td style="text-align: right;;"></td><td style=";">17-May</td><td style="text-align: right;;">3526</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">24-05-19</td><td style="text-align: right;;">11,866</td><td style="text-align: right;;"></td><td style=";">24-May</td><td style="text-align: right;;">11866</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">31-05-19</td><td style="text-align: right;;">54,079</td><td style="text-align: right;;"></td><td style=";">31-May</td><td style="text-align: right;;">54079</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">07-06-19</td><td style="text-align: right;;">13,805</td><td style="text-align: right;;"></td><td style=";">07-Jun</td><td style="text-align: right;;">13805</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">14-06-19</td><td style="text-align: right;;">8,223</td><td style="text-align: right;;"></td><td style=";">14-Jun</td><td style="text-align: right;;">8223</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">21-06-19</td><td style="text-align: right;;">5,417</td><td style="text-align: right;;"></td><td style=";">21-Jun</td><td style="text-align: right;;">5417</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="font-weight: bold;;">Less than</td><td style="text-align: right;;">21-06-19</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="font-weight: bold;text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="font-weight: bold;;">sum</td><td style="text-align: right;;">94,050</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B14</th><td style="text-align:left">=SUMIFS(<font color="Blue">$B$3:$B$10,$A$3:$A$10,"<"&B12</font>)</td></tr></tbody></table></td></tr></table><br />
 

bluelabel

Board Regular
Joined
Nov 27, 2008
Messages
76
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

Well-known Member
Joined
Jul 15, 2016
Messages
611
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:

bluelabel

Board Regular
Joined
Nov 27, 2008
Messages
76
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,150
Messages
5,628,992
Members
416,358
Latest member
grsaltzman

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
Top