Hi,
I'm trying to create a sumif formula that will add up a date range in a pivot table depending on what is selected in timeline, i.e. year, quarter, month. Currently I have two slicers, one for Vendor, one for Purchase Order No and depending on the selections the total figures for each item will change in my formula. However, I'm now stuck with the date range as when I select a particular date the pivot changes but my formula obviously doesn't factor this in because I haven't stipulated the date range. My current formula is:
=SUM(SUMIFS(Transactions[$ PO Amount],Transactions[Document Type],"Purchase Order",Transactions[Purchase Order No],$B$5:$B$100000))
The Period Date column (in the Transactions Tab) is the column I need to use as the criteria for the timeline and it needs to update the invoices/purchases orders/accruals/forecast fields with the correct totals that are shown in the pivot.
I've tried a few solutions but none are working as I want them to so I'm stuck. Any help will be appreciated.
Could you upload a file the contains the work that you have done already? Otherwise we would have to recreate something ourselves and guess what you are trying to achieve.
Hi Riny, I've uploaded the file. I thought I had attached it when I put the post up but received an error when it loaded. So I've added again
Hi Linda, please see attached. I added another pivot table sharing the same pivot cache, returning only the min and max for Period Date.
Then I added a Timeline connecting to both pt's. Then I added two arguments to the SUMIF for dates >= minimum date and <= maximum date.
I believe that achieves what you described.
