Trouble with Pivot Tables and Iserror Function

[bolwahnn]

I'm trying to sum a couple of days (a weeks worth) of data from a pivot table. My formula looks like this:

=IF(ISERROR(GETPIVOTDATA("TotalAnswered_Value",'PlatePass Email CSV'!$G$6,"ByDay_Value",F38)),0,GETPIVOTDATA("TotalAnswered_Value",'PlatePass Email CSV'!$G$6,"ByDay_Value",F38)+IF(ISERROR(GETPIVOTDATA("TotalAnswered_Value",'PlatePass Email CSV'!$G$6,"ByDay_Value",F38-1)),0,GETPIVOTDATA("TotalAnswered_Value",'PlatePass Email CSV'!$G$6,"ByDay_Value",F38-1)+IF(ISERROR(GETPIVOTDATA("TotalAnswered_Value",'PlatePass Email CSV'!$G$6,"ByDay_Value",F38-2)),0,GETPIVOTDATA("TotalAnswered_Value",'PlatePass Email CSV'!$G$6,"ByDay_Value",F38-2)+IF(ISERROR(GETPIVOTDATA("TotalAnswered_Value",'PlatePass Email CSV'!$G$6,"ByDay_Value",F38-3)),0,GETPIVOTDATA("TotalAnswered_Value",'PlatePass Email CSV'!$G$6,"ByDay_Value",F38-3)+IF(ISERROR(GETPIVOTDATA("TotalAnswered_Value",'PlatePass Email CSV'!$G$6,"ByDay_Value",F38-4)),0,GETPIVOTDATA("TotalAnswered_Value",'PlatePass Email CSV'!$G$6,"ByDay_Value",F38-4))))))

My date being referenced is 9/02/2011 and my Pivot data looks like this:
<TABLE style="WIDTH: 215pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=286><COLGROUP><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><COL style="WIDTH: 146pt; mso-width-source: userset; mso-width-alt: 7094" width=194><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 69pt; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=20 width=92>Row Labels</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 146pt; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" width=194>Sum of TotalAnswered_Value</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=20>8/29/2011</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>147</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=20>8/30/2011</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>177</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=20>9/1/2011</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>66</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=20>9/2/2011</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>70</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" class=xl68 height=20>Grand Total</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>460</TD></TR></TBODY></TABLE>

As you can see there is no data for 8/31/2011. My calculation returns 136, but as you can see I have 460. If I move the part of the calc that doesnt contain data (i.e. the F38-2 portion) I get the correct Total.

How can I fix my formula that if a day is missing, it will still give me the overall sum of all days?

Thanks,
Zeke

 

[Jerry Sullivan]

Hi Zeke and welcome to the Board,

I believe your parentheses are in the wrong places for you want to do.

To simplify things, what you want is:
=IF(ISERROR(PivotFormula1),0,(PivotFormula1))
+IF(ISERROR(PivotFormula2),0,(PivotFormula2))
+IF(ISERROR(PivotFormula3),0,(PivotFormula3))
+IF(ISERROR(PivotFormula4),0,(PivotFormula4))

what your formula is currently doing is...
=IF(ISERROR(PivotFormula1),0,(PivotFormula1)
+IF(ISERROR(PivotFormula2),0,(PivotFormula2)
+IF(ISERROR(PivotFormula3),0,(PivotFormula3)
+IF(ISERROR(PivotFormula4),0,(PivotFormula4)))))

 

[bolwahnn]

Thank you sir! You were spot on.