GETPIVIOTDATA Date Range and Sum Data

Question

I'm trying to generate a tool to review data pulled from a pivot table. The goal is to set two drop-downs of dates, and have the cell be populated with the sum of the data between those two dates.

I can get a cell to populate information of one date with the below formula, where H3 is the cell where the Start Date drop-down is located.

=GETPIVOTDATA("Sum Qty",'Data2019'!$A$3,"Description","Registration","Status","Rookie","Days (Date Added)",H3)

I can't figure out how to get the formula to recognize the End Date from cell K3, then sum all the data between those dates of the Pivot Table.

Thank you in advance,

Answer 1

Hi Evan,

Thank you for the file and detailed description you provided. I’m sorry for the late reply.

According to the file you provided, for a best practice and to make it easier to calculate, we’d suggest you you divide the date range in the drop-down box into two cells, the start date and the end date.

At the same time, since your dates are in range and you are using a drop down box to display multiple date ranges, your requirements may be realized through VBA. To ensure you get dedicated help on your concern, I have moved the thread to the correct category: Office /Excel /Microsoft Office Programming /Office 2016.

Hope you will receive suggestions from agent and forum members there.

We greatly appreciate your time and understanding. 

Best Regards,

Sukie

Answer 2

Hi Evan,

Thank you for you message in this forum.

You mentioned “I can't figure out how to get the formula to recognize the End Date from cell K3”, does it mean that it will show the error message when you use formula to recognize the End Date? Please check if the End Date in K3 is visible, you can use GETPIVOTDATA to retrieve summary data from a PivotTable, provided the summary data is visible in the report. You can refer to this article: GETPIVOTDATA function.

If the scenario above is not consistent with yours, could you provide more details about "I can't figure out how to get the formula to recognize the End Date from cell K3"?

At the same time, if you are convenient, to better understand your situation, I want to collect the source data file and Pivot Table file with you via private message. Please click on the link below to access your private message: https://answers.microsoft.com/en-us/privatemessage/inbox.

Best Regards,

Sukie