SUMPRODUCT Help

[Joneye]

I have a formula that is failing cell E:3, Its worked fine before but im thinking excel 2003 may have an issue with the commas within the bracket part of the formala.

Any help or pointers welcome.

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</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><td style=";">FUEL SURCHARGE</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="background-color: #FFFF00;;">Collection Visits</td><td style="background-color: #FFFF00;;">QTY Collected</td><td style="background-color: #FFFF00;;">Delivery Visits</td><td style="background-color: #FFFF00;;">Quantity Delivered</td><td style="text-align: right;;"></td><td style=";">RECEIVING AND ENTRY</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;background-color: #FFFF00;;">4</td><td style="text-align: center;background-color: #FFFF00;;">0</td><td style="text-align: center;background-color: #FFFF00;;">8</td><td style="text-align: center;background-color: #FFFF00;;">19</td><td style="text-align: right;;"></td><td style=";">RETRIEVAL, CARTON</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;background-color: #FFFF00;;">3</td><td style="text-align: center;background-color: #FFFF00;;">0</td><td style="text-align: center;background-color: #FFFF00;;">16</td><td style="text-align: center;background-color: #FFFF00;;">23</td><td style="text-align: right;;"></td><td style=";">RETRIEVAL, CARTON - RUSH</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;background-color: #FFFF00;;">0</td><td style="text-align: center;background-color: #FFFF00;;">0</td><td style="text-align: center;background-color: #FFFF00;;">0</td><td style="text-align: center;background-color: #FFFF00;;">0</td><td style="text-align: right;;"></td><td style=";">RETRIEVAL, FILE</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;background-color: #FFFF00;;">5</td><td style="text-align: center;background-color: #FFFF00;;">382.4</td><td style="text-align: center;background-color: #FFFF00;;">12</td><td style="text-align: center;background-color: #FFFF00;;">17</td><td style="text-align: right;;"></td><td style=";">STORAGE, NEW</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;background-color: #FFFF00;;">1</td><td style="text-align: center;background-color: #FFFF00;;">44.8</td><td style="text-align: center;background-color: #FFFF00;;">3</td><td style="text-align: center;background-color: #FFFF00;;">25.84</td><td style="text-align: right;;"></td><td style=";">STORAGE, WITHDRAWN</td><td style=";">x</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;background-color: #FFFF00;;">4</td><td style="text-align: center;background-color: #FFFF00;;">117</td><td style="text-align: center;background-color: #FFFF00;;">7</td><td style="text-align: center;background-color: #FFFF00;;">#VALUE!</td><td style="text-align: right;;"></td><td style=";">TRANSPORTATION HANDLING</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;background-color: #FFFF00;;">4</td><td style="text-align: center;background-color: #FFFF00;;">121.6</td><td style="text-align: center;background-color: #FFFF00;;">12</td><td style="text-align: center;background-color: #FFFF00;;">10</td><td style="text-align: right;;"></td><td style=";">TRIP CHARGE, NEXT DAY DELIVERY</td><td style=";">x</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;background-color: #FFFF00;;"></td><td style="text-align: center;background-color: #FFFF00;;"></td><td style="text-align: center;background-color: #FFFF00;;"></td><td style="text-align: center;background-color: #FFFF00;;"></td><td style="text-align: right;;"></td><td style=";">TRIP CHARGE, PICKUP</td><td style=";">x</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;background-color: #FFFF00;;">21</td><td style="text-align: center;background-color: #FFFF00;;">665.8</td><td style="text-align: center;background-color: #FFFF00;;">58</td><td style="text-align: center;background-color: #FFFF00;;">#VALUE!</td><td style="text-align: right;;"></td><td style=";">TRIP CHARGE, RUSH DELIVERY</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:2.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Data</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$15:$A$56=A3</font>),--(<font color="Red">$G$15:$G$56=$G$10</font>),$E$15:$E$56</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$15:$A$56=A3</font>),--(<font color="Red">$G$15:$G$56=$G$6</font>),E$15:$E$56</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$15:$A$56=A3</font>),--(<font color="Red">$G$15:$G$56=$G$9</font>),$E$15:$E$56</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$15:$A$56=A3</font>),--(<font color="Red">$G$15:$G$56=$G$3</font>),$E$15:$E$56</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B4</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$15:$A$56=A4</font>),--(<font color="Red">$G$15:$G$56=$G$10</font>),$E$15:$E$56</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C4</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$15:$A$56=A4</font>),--(<font color="Red">$G$15:$G$56=$G$6</font>),E$15:$E$56</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D4</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$15:$A$56=A4</font>),--(<font color="Red">$G$15:$G$56=$G$9</font>),$E$15:$E$56</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E4</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$15:$A$56=A4</font>),--(<font color="Red">$G$15:$G$56=$G$3</font>),$E$15:$E$56</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B5</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$15:$A$56=A5</font>),--(<font color="Red">$G$15:$G$56=$G$10</font>),$E$15:$E$56</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C5</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$15:$A$56=A5</font>),--(<font color="Red">$G$15:$G$56=$G$6</font>),E$15:$E$56</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D5</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$15:$A$56=A5</font>),--(<font color="Red">$G$15:$G$56=$G$9</font>),$E$15:$E$56</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E5</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$15:$A$56=A5</font>),--(<font color="Red">$G$15:$G$56=$G$3</font>),$E$15:$E$56</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B6</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$15:$A$56=A6</font>),--(<font color="Red">$G$15:$G$56=$G$10</font>),$E$15:$E$56</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C6</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$15:$A$56=A6</font>),--(<font color="Red">$G$15:$G$56=$G$6</font>),E$15:$E$56</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D6</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$15:$A$56=A6</font>),--(<font color="Red">$G$15:$G$56=$G$9</font>),$E$15:$E$56</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E6</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$15:$A$56=A6</font>),--(<font color="Red">$G$15:$G$56=$G$3</font>),$E$15:$E$56</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B7</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$15:$A$56=A7</font>),--(<font color="Red">$G$15:$G$56=$G$10</font>),$E$15:$E$56</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C7</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$15:$A$56=A7</font>),--(<font color="Red">$G$15:$G$56=$G$6</font>),E$15:$E$56</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D7</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$15:$A$56=A7</font>),--(<font color="Red">$G$15:$G$56=$G$9</font>),$E$15:$E$56</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E7</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$15:$A$56=A7</font>),--(<font color="Red">$G$15:$G$56=$G$3</font>),$E$15:$E$56</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B8</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$15:$A$56=A8</font>),--(<font color="Red">$G$15:$G$56=$G$10</font>),$E$15:$E$56</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C8</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$15:$A$56=A8</font>),--(<font color="Red">$G$15:$G$56=$G$6</font>),E$15:$E$56</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D8</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$15:$A$56=A8</font>),--(<font color="Red">$G$15:$G$56=$G$9</font>),$E$15:$E$56</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E8</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$15:$A$56=A8</font>),--(<font color="Red">$G$15:$G$56={"Retrieval, Carton","Retrieval, File"}</font>),E$15:$E$56</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B9</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$15:$A$56=A9</font>),--(<font color="Red">$G$15:$G$56=$G$10</font>),$E$15:$E$56</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C9</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$15:$A$56=A9</font>),--(<font color="Red">$G$15:$G$56=$G$6</font>),E$15:$E$56</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D9</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$15:$A$56=A9</font>),--(<font color="Red">$G$15:$G$56=$G$9</font>),$E$15:$E$56</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E9</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$15:$A$56=A9</font>),--(<font color="Red">$G$15:$G$56=$G$3</font>),$E$15:$E$56</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B11</th><td style="text-align:left">=SUM(<font color="Blue">B3:B9</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C11</th><td style="text-align:left">=SUM(<font color="Blue">C3:C9</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D11</th><td style="text-align:left">=SUM(<font color="Blue">D3:D9</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E11</th><td style="text-align:left">=SUM(<font color="Blue">E3:E9</font>)</td></tr></tbody></table></td></tr></table><br />

 

[T. Valko]

I have a formula that is failing cell E:3, Its worked fine before but im thinking excel 2003 may have an issue with the commas within the bracket part of the formala.

Any help or pointers welcome.

Excel 2003<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</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><TD>FUEL SURCHARGE</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="BACKGROUND-COLOR: #ffff00">Collection Visits</TD><TD style="BACKGROUND-COLOR: #ffff00">QTY Collected</TD><TD style="BACKGROUND-COLOR: #ffff00">Delivery Visits</TD><TD style="BACKGROUND-COLOR: #ffff00">Quantity Delivered</TD><TD style="TEXT-ALIGN: right"></TD><TD>RECEIVING AND ENTRY</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">4</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">0</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">8</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">19</TD><TD style="TEXT-ALIGN: right"></TD><TD>RETRIEVAL, CARTON</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">3</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">0</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">16</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">23</TD><TD style="TEXT-ALIGN: right"></TD><TD>RETRIEVAL, CARTON - RUSH</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">0</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">0</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">0</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: right"></TD><TD>RETRIEVAL, FILE</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">5</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">382.4</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">12</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: right"></TD><TD>STORAGE, NEW</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">1</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">44.8</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">3</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">25.84</TD><TD style="TEXT-ALIGN: right"></TD><TD>STORAGE, WITHDRAWN</TD><TD>x</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">8</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">4</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">117</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">7</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">#VALUE!</TD><TD style="TEXT-ALIGN: right"></TD><TD>TRANSPORTATION HANDLING</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">9</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">4</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">121.6</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">12</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right"></TD><TD>TRIP CHARGE, NEXT DAY DELIVERY</TD><TD>x</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">10</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center"></TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center"></TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center"></TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD><TD>TRIP CHARGE, PICKUP</TD><TD>x</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">11</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">21</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">665.8</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">58</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">#VALUE!</TD><TD style="TEXT-ALIGN: right"></TD><TD>TRIP CHARGE, RUSH DELIVERY</TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>

Data​

<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>B3</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--($A$15:$A$56=A3),--($G$15:$G$56=$G$10),$E$15:$E$56)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>C3</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--($A$15:$A$56=A3),--($G$15:$G$56=$G$6),E$15:$E$56)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>D3</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--($A$15:$A$56=A3),--($G$15:$G$56=$G$9),$E$15:$E$56)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>E3</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--($A$15:$A$56=A3),--($G$15:$G$56=$G$3),$E$15:$E$56)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>B4</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--($A$15:$A$56=A4),--($G$15:$G$56=$G$10),$E$15:$E$56)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>C4</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--($A$15:$A$56=A4),--($G$15:$G$56=$G$6),E$15:$E$56)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>D4</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--($A$15:$A$56=A4),--($G$15:$G$56=$G$9),$E$15:$E$56)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>E4</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--($A$15:$A$56=A4),--($G$15:$G$56=$G$3),$E$15:$E$56)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>B5</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--($A$15:$A$56=A5),--($G$15:$G$56=$G$10),$E$15:$E$56)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>C5</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--($A$15:$A$56=A5),--($G$15:$G$56=$G$6),E$15:$E$56)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>D5</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--($A$15:$A$56=A5),--($G$15:$G$56=$G$9),$E$15:$E$56)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>E5</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--($A$15:$A$56=A5),--($G$15:$G$56=$G$3),$E$15:$E$56)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>B6</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--($A$15:$A$56=A6),--($G$15:$G$56=$G$10),$E$15:$E$56)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>C6</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--($A$15:$A$56=A6),--($G$15:$G$56=$G$6),E$15:$E$56)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>D6</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--($A$15:$A$56=A6),--($G$15:$G$56=$G$9),$E$15:$E$56)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>E6</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--($A$15:$A$56=A6),--($G$15:$G$56=$G$3),$E$15:$E$56)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>B7</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--($A$15:$A$56=A7),--($G$15:$G$56=$G$10),$E$15:$E$56)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>C7</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--($A$15:$A$56=A7),--($G$15:$G$56=$G$6),E$15:$E$56)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>D7</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--($A$15:$A$56=A7),--($G$15:$G$56=$G$9),$E$15:$E$56)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>E7</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--($A$15:$A$56=A7),--($G$15:$G$56=$G$3),$E$15:$E$56)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>B8</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--($A$15:$A$56=A8),--($G$15:$G$56=$G$10),$E$15:$E$56)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>C8</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--($A$15:$A$56=A8),--($G$15:$G$56=$G$6),E$15:$E$56)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>D8</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--($A$15:$A$56=A8),--($G$15:$G$56=$G$9),$E$15:$E$56)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>E8</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--($A$15:$A$56=A8),--($G$15:$G$56={"Retrieval, Carton","Retrieval, File"}),E$15:$E$56)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>B9</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--($A$15:$A$56=A9),--($G$15:$G$56=$G$10),$E$15:$E$56)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>C9</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--($A$15:$A$56=A9),--($G$15:$G$56=$G$6),E$15:$E$56)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>D9</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--($A$15:$A$56=A9),--($G$15:$G$56=$G$9),$E$15:$E$56)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>E9</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--($A$15:$A$56=A9),--($G$15:$G$56=$G$3),$E$15:$E$56)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>B11</TH><TD style="TEXT-ALIGN: left">=SUM(B3:B9)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>C11</TH><TD style="TEXT-ALIGN: left">=SUM(C3:C9)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>D11</TH><TD style="TEXT-ALIGN: left">=SUM(D3:D9)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>E11</TH><TD style="TEXT-ALIGN: left">=SUM(E3:E9)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

That formula in E8 is screwing things up.

Try it like this:

=SUMPRODUCT(--($A$15:$A$56=A8),--(ISNUMBER(MATCH($G$15:$G$56,{"Retrieval, Carton","Retrieval, File"},0))),E$15:$E$56)

 

[Joneye]

T. Valko


Im loving that science - it worked a treat - im now gonna try and understand why it worked.

many thanks for the help

 

[T. Valko]

T. Valko


Im loving that science - it worked a treat - im now gonna try and understand why it worked.

many thanks for the help

You're welcome. Thanks for the feedback!