Pivot Table Count

[paulstan]

I have a data field with 3 ‘Count Of’ pieces of data in a Pivot Table. The first is an overall total, the second is a count of errors (Yes/No from the spreadsheet) and the third is a count of items returned (Yes/No from the spreadsheet).

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
All rows return exactly the same number for example 10, 10, 10. Both Yes/Nos are being added up and just replicating the Overall total field. What I would like is something where the figures under ‘errors’ and ‘returned’ are counting Yes entries only. So if there were 2 errors and 3 returned then the row would read 10, 8, 7

<o></o>
What I’m trying to achieve is to have the 2 Yes/No lists to recognise the Yes only selection and subtract from the overall total.
Thanks for reading.

 

[GlennUK]

Can you show a sample of your base data, and the layout of your pivottable?

 

[paulstan]

Glenn (and all)

I've enclosed a snapshot of the data. Hope it is clear what I'm trying to achieve.

Regards

Paul

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH><TH>K</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #00ffff; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">TYPE</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #00ffff; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">CUST NAME</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #00ffff; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">ID</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #00ffff; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">OP TYPE</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #00ffff; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">OP REASON</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #00ffff; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">ERROR Y/N</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #00ffff; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">ERROR DETAILS (if applicable)</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #00ffff; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">OPERATORS NAME</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #00ffff; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">CHKD BY </TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #00ffff; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">DATE</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #00ffff; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">RET'D Y/N</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">ONE</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">ALPHA</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">A111111</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">TYPE 1</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">EXAMPLE 1</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Y</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">REASON 1</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">GREEN</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">MR A</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">16/03/2011</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Y</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">ONE</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">BRAVO</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">B111111</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">TYPE 2</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">EXAMPLE 2</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Y</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">REASON 1</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">GREEN</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">MR B</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">16/03/2011</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Y</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">THREE</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">CHARLIE</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">C111111</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">TYPE 1</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">EXAMPLE 3</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Y</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">REASON 2</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">BROWN</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">MR C</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">16/03/2011</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Y</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">FOUR</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">DELTA</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">D111111</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">TYPE 1</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">EXAMPLE 4</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Y</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">REASON 3</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">RED</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">MR D</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">29/03/2011</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">N</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">FOUR</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">ECHO</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">E111111</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">TYPE 1</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">EXAMPLE 5</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Y</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">REASON 5</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">BLUE</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">MR D</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">01/04/2011</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">N</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">TWO</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">FOXTROT</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">F111111</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">TYPE 1</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">EXAMPLE 6</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">N</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">RED</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">MR G</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">01/04/2011</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">N</TD></TR></TBODY></TABLE>

Apr 11​

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #000000; COLOR: #ffffff; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold">ERROR DETAILS (if applicable)</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #000000; COLOR: #ffffff; FONT-WEIGHT: bold">Count of ID</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #000000; COLOR: #ffffff; FONT-WEIGHT: bold">Count of ERROR Y/N</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #000000; COLOR: #ffffff; FONT-WEIGHT: bold">Count of RET'D Y/N</TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="BORDER-TOP: black 1px solid">(blank)</TD><TD style="TEXT-ALIGN: center; BORDER-TOP: black 1px solid">8</TD><TD style="TEXT-ALIGN: center; BORDER-TOP: black 1px solid">8</TD><TD style="TEXT-ALIGN: center; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">8</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD>REASON 1</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center; BORDER-RIGHT: black 1px solid">3</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD>REASON 2</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center; BORDER-RIGHT: black 1px solid">4</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD>REASON 3</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center; BORDER-RIGHT: black 1px solid">3</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD>REASON 5</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center; BORDER-RIGHT: black 1px solid">5</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD>REASON 11</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center; BORDER-RIGHT: black 1px solid">1</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD>REASON 7</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center; BORDER-RIGHT: black 1px solid">2</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD>REASON 6</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center; BORDER-RIGHT: black 1px solid">5</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD style="BORDER-BOTTOM: black 1px solid">REASON 4</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">4</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">4</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-RIGHT: black 1px solid">4</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c0c0c0; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Total Referrals</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c0c0c0; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold">35</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold">35</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">35</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">19</TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">20</TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">21</TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">Count of all entries</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">Count of ERRORS. Want to only count the YES records</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">Count of RET'D items. Want to only count the YES records</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">Want a total of all entries less ERRORS and RET'D records</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">22</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>

report​

 

[GlennUK]

A simple pivottable to do one item looks like this:

Excel Workbook
	A	B	C	D
1	*	*	*	*
2	*	*	*	*
3	Count of ID	ERROR Y/N	*	*
4	ERROR DETAILS (if applicable)	N	Y	Grand Total
5	REASON 1	*	18	18
6	REASON 2	*	8	8
7	REASON 3	*	8	8
8	REASON 5	8	8	16
9	Grand Total	8	42	50
Sheet4

Can you do multiple tables to get what you want? I'm not sure that counts of an item in one field can be used within a table for a calculated value with another item in another field. I will carry on experimenting to see if I can do more in a single table.

Can you explain exactly what you mean by "Want a total of all entries less ERRORS and RET'D records"? Is that then only entries that are N for both?

 

[paulstan]

Hi Glenn

Thanks for your response. I've only been looking at pivottables for the last week, so not sure if my limited knowledge at present would stretch to more than one table.

My question about "want a total of all entries..." does sound slightly ambiguous. What I'm after is a sum as follows:

Count of ID = 10
less Count of Error (Ys only), say 2 Yes records
less Count of Ret'd (Ys only), say 4 Yes records
Total = 4

Many thanks for your time and effort.

Regards

Paul S

 

[GlennUK]

...
What I'm after is a sum as follows:

Count of ID = 10
less Count of Error (Ys only), say 2 Yes records
less Count of Ret'd (Ys only), say 4 Yes records
Total = 4

What if the Error Y's and Ret'd Y's overlap? Which they do in your sample data ... count of 6, Error Ys= 5, Retd Ys = 3.

 

[paulstan]

Glenn

Should we pretend that I never mentioned a total figure (Col E of the pivot table snapshot)!!

Back to the pivot table (my post) and using Reason 5 example, here goes:

Columns A & B - Total 5 records - 5 to be displayed in Column B

Column C - say there were 4 errors (YES) - 4 to be displayed

Column D - say there was 2 errors (YES) (this figure will never be greater than Column C) - 2 to be displayed

... and no total!!

Thanks for your time (and patience!!)

Paul S

 

[GlennUK]

Well, can I ask why you don't just use COUNTIF on the raw data? It seems like you don't even need to PivotTable the data.

 

[paulstan]

Glenn

I have tried that and it works.

Many thanks for pointing this novice in the right direction!

Regards

Paul S

 

[GlennUK]

That's great! My pleasure to help out.