Access multiple values with getpivotdata

[barteklavlar]

I am currently setting up a worksheet for tracking costs. I have the values set up like this:

=IF(ISERROR(GETPIVOTDATA("Amount", 'Itemized Costs'!$A$3,"Date",D6,"Cost Center",C51)),0,GETPIVOTDATA("Amount", 'Itemized Costs'!$A$3,"Date",D6,"Cost Center",C51))

This works fine once I enter the date in D6 and returns the costs associated with the specific category for that date. There are costs though that are entered in dates before. Say the first worksheet will be Aug 4 2011 but I want to include all the costs from Jan 1 2011 until Aug 4 2011 while the rest of the sheets are fine just using the single day as the numbers accummulate? Is there a way of doing that by modifying this formula?
thanks in advance for any help

 

[barteklavlar]

Never mind sorry, I figured it out by running total by date not cost center.


Also to kind of add to this, this is of more importance to me, my pivot table that I am getting the costs from. I have different categories and I want it to add up. They are date, contractor, invoice no., cost center, amount
I would like it to add up the amounts that have the same cost center and date, but currently it is summing up the amounts per day
ie separators 10000, well head 20000
it is adding them up as separators 10000 well head 30000
any help would be appreciated

 

[barteklavlar]

Well that fixed one of my problems, but has created another problem

This is my pivot table where I am storing my data.
<table border="0" cellpadding="0" cellspacing="0" width="318"><col style="mso-width-source:userset;mso-width-alt:5595;width:115pt" width="153"> <col style="mso-width-source:userset;mso-width-alt:2157;width:44pt" width="59"> <col style="mso-width-source:userset;mso-width-alt:3876;width:80pt" width="106"> <tbody><tr style="height:12.75pt" height="17"> <td colspan="3" rowspan="2" class="xl109" style="height:25.5pt; width:239pt" height="34" width="318">Itemized Costs</td> </tr> <tr style="height:12.75pt" height="17"> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt;font-size:10.0pt;color:black;font-weight: 700;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid #4F81BD;border-right:none;border-bottom: .5pt solid #4F81BD;border-left:none" height="17">Cost Center</td> <td style="font-size:10.0pt;color:black;font-weight:700;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Arial; border-top:.5pt solid #4F81BD;border-right:none;border-bottom:.5pt solid #4F81BD; border-left:none">Date</td> <td style="font-size:10.0pt;color:black;font-weight:700;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Arial; border-top:.5pt solid #4F81BD;border-right:none;border-bottom:.5pt solid #4F81BD; border-left:none">Sum of Amount</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt;font-size:10.0pt;color:black;font-weight: 700;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid #DCE6F1;border-right:none;border-bottom: .5pt solid #DCE6F1;border-left:none" height="17">Installation Labour</td> <td style="font-size:10.0pt;color:black;font-weight:700;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Arial; border-top:.5pt solid #DCE6F1;border-right:none;border-bottom:.5pt solid #DCE6F1; border-left:none">
</td> <td class="xl107" style="font-size:10.0pt;color:black;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid #DCE6F1;border-right:none;border-bottom: .5pt solid #DCE6F1;border-left:none">
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt;font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid #DCE6F1;border-right:none;border-bottom: .5pt solid #DCE6F1;border-left:none" height="17">
</td> <td class="xl108" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid #DCE6F1;border-right:none;border-bottom: .5pt solid #DCE6F1;border-left:none" align="right">4-Aug-11</td> <td class="xl107" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid #DCE6F1;border-right:none;border-bottom: .5pt solid #DCE6F1;border-left:none" align="right">$3,870.00</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt;font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid #DCE6F1;border-right:none;border-bottom: .5pt solid #DCE6F1;border-left:none" height="17">
</td> <td class="xl108" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid #DCE6F1;border-right:none;border-bottom: .5pt solid #DCE6F1;border-left:none" align="right">5-Aug-11</td> <td class="xl107" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid #DCE6F1;border-right:none;border-bottom: .5pt solid #DCE6F1;border-left:none" align="right">$3,850.00</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt;font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid #DCE6F1;border-right:none;border-bottom: .5pt solid #DCE6F1;border-left:none" height="17">
</td> <td class="xl108" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid #DCE6F1;border-right:none;border-bottom: .5pt solid #DCE6F1;border-left:none" align="right">6-Aug-11</td> <td class="xl107" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid #DCE6F1;border-right:none;border-bottom: .5pt solid #DCE6F1;border-left:none" align="right">$3,732.00</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt;font-size:10.0pt;color:black;font-weight: 700;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid #DCE6F1;border-right:none;border-bottom: .5pt solid #DCE6F1;border-left:none" height="17">(blank)</td> <td style="font-size:10.0pt;color:black;font-weight:700;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Arial; border-top:.5pt solid #DCE6F1;border-right:none;border-bottom:.5pt solid #DCE6F1; border-left:none">
</td> <td class="xl107" style="font-size:10.0pt;color:black;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid #DCE6F1;border-right:none;border-bottom: .5pt solid #DCE6F1;border-left:none">
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt;font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid #DCE6F1;border-right:none;border-bottom: .5pt solid #DCE6F1;border-left:none" height="17">
</td> <td class="xl108" style="font-size:10.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid #DCE6F1;border-right:none;border-bottom: .5pt solid #DCE6F1;border-left:none">(blank)</td> <td class="xl107" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid #DCE6F1;border-right:none;border-bottom: .5pt solid #DCE6F1;border-left:none" align="right">$0.00</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt;font-size:10.0pt;color:black;font-weight: 700;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid #DCE6F1;border-right:none;border-bottom: .5pt solid #DCE6F1;border-left:none" height="17">Surveying</td> <td style="font-size:10.0pt;color:black;font-weight:700;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Arial; border-top:.5pt solid #DCE6F1;border-right:none;border-bottom:.5pt solid #DCE6F1; border-left:none">
</td> <td class="xl107" style="font-size:10.0pt;color:black;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid #DCE6F1;border-right:none;border-bottom: .5pt solid #DCE6F1;border-left:none">
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt;font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid #DCE6F1;border-right:none;border-bottom: .5pt solid #DCE6F1;border-left:none" height="17">
</td> <td class="xl108" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid #DCE6F1;border-right:none;border-bottom: .5pt solid #DCE6F1;border-left:none" align="right">2-Aug-11</td> <td class="xl107" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid #DCE6F1;border-right:none;border-bottom: .5pt solid #DCE6F1;border-left:none" align="right">$1,519.50</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt;font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid #DCE6F1;border-right:none;border-bottom: .5pt solid #DCE6F1;border-left:none" height="17">
</td> <td class="xl108" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid #DCE6F1;border-right:none;border-bottom: .5pt solid #DCE6F1;border-left:none" align="right">3-Aug-11</td> <td class="xl107" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid #DCE6F1;border-right:none;border-bottom: .5pt solid #DCE6F1;border-left:none" align="right">$1,519.50</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt;font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid #DCE6F1;border-right:none;border-bottom: .5pt solid #DCE6F1;border-left:none" height="17">
</td> <td class="xl108" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid #DCE6F1;border-right:none;border-bottom: .5pt solid #DCE6F1;border-left:none" align="right">4-Aug-11</td> <td class="xl107" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid #DCE6F1;border-right:none;border-bottom: .5pt solid #DCE6F1;border-left:none" align="right">$5,659.50</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt;font-size:10.0pt;color:black;font-weight: 700;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid #DCE6F1;border-right:none;border-bottom: .5pt solid #DCE6F1;border-left:none" height="17">Construction Supervision</td> <td style="font-size:10.0pt;color:black;font-weight:700;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Arial; border-top:.5pt solid #DCE6F1;border-right:none;border-bottom:.5pt solid #DCE6F1; border-left:none">
</td> <td class="xl107" style="font-size:10.0pt;color:black;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid #DCE6F1;border-right:none;border-bottom: .5pt solid #DCE6F1;border-left:none">
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt;font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid #DCE6F1;border-right:none;border-bottom: none;border-left:none" height="17">
</td> <td class="xl108" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid #DCE6F1;border-right:none;border-bottom: none;border-left:none" align="right">7-Aug-11</td> <td class="xl107" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid #DCE6F1;border-right:none;border-bottom: none;border-left:none" align="right">$8,470.00</td> </tr> </tbody></table>
As you see, the numbers work alright, and when I run the formula from the first post it works properly and I get the cost per cost centre per day. On august 4 though, it is taking the installation amount and adding it to the survey amount to get the 5659.5 amount. Can anyone figure out how to stop this from happening as it is just summing the day as it moves down the pivot table?