Hi there
Suppose I have a pivot table with two columns, date and volume. I want to get the total volume for a date range (ie. a week) with getpivotdata like
=getpivotdata("Volume",$A$1, "Date","1/7/2013 - 1/7/2013 +6")
How can I get it to work?
Thank you.
perhaps
=SUMPRODUCT(IFERROR(GETPIVOTDATA("Volume",$A$1,"Date",DATE(2013,7,1)+{0,1,2,3,4,5}),0))
Josie
if at first you don't succeed try doing it the way your wife told you to
Thank you, JosephP. That works brilliantly!
What if the date range is a lot longer and presented like
1/7/2013 - 25/9/2013
How can I present the array for the sumproduct function?
assume your dates are in cells g3 and g4
=SUMPRODUCT(IFERROR(GETPIVOTDATA("Volume",$A$1,"Date",ROW(INDIRECT(G3&":"&G4))),0))
this must be array-entered using ctrl+shift+enter
Thank you very much! This will make things a lot easier.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks