Hi there,
I'm baffled by a pivot table problem I've encountered in Excel 2010. See pivot table below, which is using 3 slicers (one for the year, and two others for other kinds of qualities not shown in the table).
<TABLE style="WIDTH: 598pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=793><COLGROUP><COL style="WIDTH: 146pt; mso-width-source: userset; mso-width-alt: 6772" width=194><COL style="WIDTH: 119pt; mso-width-source: userset; mso-width-alt: 5515" width=158><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1698" span=7 width=49><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3421" width=98><TBODY><TR style="HEIGHT: 12.5pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 146pt; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; HEIGHT: 12.5pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 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=xl63 height=17 width=194>Sum of violation counter</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 119pt; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 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=xl64 width=158>Column Labels</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 37pt; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 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=xl64 width=49></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 37pt; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 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=xl64 width=49></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 37pt; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 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=xl64 width=49></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 37pt; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 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=xl64 width=49></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 37pt; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 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=xl64 width=49></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 37pt; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 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=xl64 width=49></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 37pt; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 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=xl64 width=49></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 74pt; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" class=xl65 width=98></TD></TR><TR style="HEIGHT: 12.5pt" height=17><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; HEIGHT: 12.5pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" class=xl66 height=17>Row Labels</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>2003</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>2004</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>2005</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>2006</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>2007</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>2008</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>2009</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>2010</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" class=xl67>Grand Total</TD></TR><TR style="HEIGHT: 12.5pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=17>Aircraft</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>2</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>7</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>3</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>6</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>0</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>0</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>18</TD></TR><TR style="HEIGHT: 12.5pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=17>Auto</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>26</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>7</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>10</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>23</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>4</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>7</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>0</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>78</TD></TR><TR style="HEIGHT: 12.5pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; HEIGHT: 12.5pt; COLOR: black; FONT-SIZE: 10pt; 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=xl69 height=17>Grand Total</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; 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=xl70 align=right>28</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; 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=xl70 align=right>14</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; 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=xl70 align=right>13</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; 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=xl70 align=right>29</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; 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=xl70 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; 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=xl70 align=right>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; 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=xl70 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; 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=xl70 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" class=xl71 align=right>96</TD></TR></TBODY></TABLE>
The trouble is that the numbers in the column for 2003 should be 1 for aircraft and 25 for auto, for a grand total of 26. I've done a manual count in my data set using auto filter, and have checked against a different pivot table that uses filters specific to that pivot table (rather than slicers). As far as I can tell, this particular slicer-related pivot table above is counting the number of records rather than summing them. There are 28 records that fit the slicer qualifications.
Other info:
--the "violation counter" field in the data has an IF formula that yields a 1 if another field has the word "yes" and yields a 0 for anything else. For my particular slicer settings, the violation counter field has 26 ones and 2 zeros. The sum should be 26.
-- I have manually checked the results for all the other years (2004-2010), and those sums are accurate.
-- I have changed the IF formula to produce a blank ("") instead of zero, but it doesn't change the results. Still the same problem for 2003
Could someone please help? I would prefer not to move away from slicers because I've got other pivot tables relying on the slicers. I also would like to figure out this problem, because this gives me pause about using the pivot tables in Excel 2010 (i.e., I feel I'm lucky to have caught this issue; what other issues am I not catching?)
Anyone got any ideas? Advance thanks for your help.
Mike
I'm baffled by a pivot table problem I've encountered in Excel 2010. See pivot table below, which is using 3 slicers (one for the year, and two others for other kinds of qualities not shown in the table).
<TABLE style="WIDTH: 598pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=793><COLGROUP><COL style="WIDTH: 146pt; mso-width-source: userset; mso-width-alt: 6772" width=194><COL style="WIDTH: 119pt; mso-width-source: userset; mso-width-alt: 5515" width=158><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1698" span=7 width=49><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3421" width=98><TBODY><TR style="HEIGHT: 12.5pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 146pt; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; HEIGHT: 12.5pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 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=xl63 height=17 width=194>Sum of violation counter</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 119pt; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 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=xl64 width=158>Column Labels</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 37pt; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 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=xl64 width=49></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 37pt; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 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=xl64 width=49></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 37pt; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 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=xl64 width=49></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 37pt; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 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=xl64 width=49></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 37pt; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 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=xl64 width=49></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 37pt; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 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=xl64 width=49></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 37pt; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 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=xl64 width=49></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 74pt; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" class=xl65 width=98></TD></TR><TR style="HEIGHT: 12.5pt" height=17><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; HEIGHT: 12.5pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" class=xl66 height=17>Row Labels</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>2003</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>2004</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>2005</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>2006</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>2007</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>2008</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>2009</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>2010</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" class=xl67>Grand Total</TD></TR><TR style="HEIGHT: 12.5pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=17>Aircraft</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>2</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>7</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>3</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>6</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>0</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>0</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>18</TD></TR><TR style="HEIGHT: 12.5pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=17>Auto</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>26</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>7</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>10</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>23</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>4</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>7</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>0</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>78</TD></TR><TR style="HEIGHT: 12.5pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; HEIGHT: 12.5pt; COLOR: black; FONT-SIZE: 10pt; 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=xl69 height=17>Grand Total</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; 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=xl70 align=right>28</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; 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=xl70 align=right>14</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; 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=xl70 align=right>13</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; 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=xl70 align=right>29</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; 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=xl70 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; 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=xl70 align=right>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; 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=xl70 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; 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=xl70 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" class=xl71 align=right>96</TD></TR></TBODY></TABLE>
The trouble is that the numbers in the column for 2003 should be 1 for aircraft and 25 for auto, for a grand total of 26. I've done a manual count in my data set using auto filter, and have checked against a different pivot table that uses filters specific to that pivot table (rather than slicers). As far as I can tell, this particular slicer-related pivot table above is counting the number of records rather than summing them. There are 28 records that fit the slicer qualifications.
Other info:
--the "violation counter" field in the data has an IF formula that yields a 1 if another field has the word "yes" and yields a 0 for anything else. For my particular slicer settings, the violation counter field has 26 ones and 2 zeros. The sum should be 26.
-- I have manually checked the results for all the other years (2004-2010), and those sums are accurate.
-- I have changed the IF formula to produce a blank ("") instead of zero, but it doesn't change the results. Still the same problem for 2003
Could someone please help? I would prefer not to move away from slicers because I've got other pivot tables relying on the slicers. I also would like to figure out this problem, because this gives me pause about using the pivot tables in Excel 2010 (i.e., I feel I'm lucky to have caught this issue; what other issues am I not catching?)
Anyone got any ideas? Advance thanks for your help.
Mike
