Using GETPIVOTDATA for multiple dates

Question

Hello all

I have a pivot report which shows number of passengers that we have booked per day by country. I want to create a sheet elsewhere where you can type to and from dates into cells and then it will return the results. I tried using a SUMIF first of all however the number of countries in the pivot changes so referring to a particular column won't always work as the countries will move. I've tried using GETPIVOTDATA however I don't know how to do this without specifiying a particular date in the formula. Any ideas?

Passengers     Country

Date       USA     UK

04/06/2010      4     67

05/06/2010     25     8

06/06/2010     11           0

Grand Total      4     92

--

James.

Answer 1

1.  This result can be obtained by filtering on the Date field in the pivot table. 

2. If you still want to type the dates in in another sheet and get the results you can setup a copy of the pivot table on that sheet, and write a macro that responds to the entry of two dates by automatically filtering the data.
3. You can use SUMIFS or SUMPRODUCT against the original data rather than the pivot table.  Suppose the raw data is:

    A            B                 C     (Sheet1)

1 Date    Country   Passengers

2 1/1/01    France      3

You enter your two dates on another sheet in A1 and B1  and in C1 the country then the formula would be

=SUMPRODUCT(--(Sheet1!A:A>=A1),--(Sheet1!A:A<=B1),--(Sheet1!B:B=C1),Sheet1!C:C)

---

If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire

Answer 2

What result do you want?  Number of passengers between the 2 specified dates?  By country?  For all countries?

Can't you put another PT in the other sheet that gives you the desired results?

---

Tushar Mehta (Technology and Operations Consulting)

www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)

Microsoft MVP Excel 2000-Present