Error Help / iserror formula?

[Joneye]

This is a snap shot of data, the end result I wish to acheive is that when I receive an #N/A in C7 or perhaps others that when I wish to count in cell H7 I get a number rather than another #N/A.

So the result in cellL

H7 using the data shown would be 0.
H8 0
H9 7
H10 0
H11 0
H12 0
H13 5

<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;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></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><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Total</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">63</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Volume By Region</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Error Checking</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">OK</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Development</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Region</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Count</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Region</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Total</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="border-top: 1px solid black;;">Balmoral Gardens </td><td style="text-align: right;border-top: 1px solid black;;">#N/A</td><td style="text-align: center;border-top: 1px solid black;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="border-top: 1px solid black;;">Yorkshire</td><td style="text-align: right;border-top: 1px solid black;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Broughton Manor NW</td><td style="text-align: right;;">#N/A</td><td style="text-align: center;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">North West</td><td style="text-align: right;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Broughton Manor SM</td><td style=";">South Midlands </td><td style="text-align: center;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Midlands</td><td style="text-align: right;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Castle View </td><td style=";">Midlands</td><td style="text-align: center;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Eastern</td><td style="text-align: right;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">Chestnut Walk </td><td style=";">Midlands </td><td style="text-align: center;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Lancashire</td><td style="text-align: right;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">Church Gresley </td><td style=";">Midlands </td><td style="text-align: center;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">South Wales</td><td style="text-align: right;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">City Wharf </td><td style=";">Midlands </td><td style="text-align: center;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">South Midlands</td><td style="text-align: right;;">#N/A</td></tr></tbody></table><p style="width:5.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">Dashboard</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">D4</th><td style="text-align:left">=SUM(<font color="Blue">D7:D500</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D5</th><td style="text-align:left">=IF(<font color="Blue">D4='Part Exchange Schedule'!D3,"OK","Error"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B7</th><td style="text-align:left">=Data!B5</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C7</th><td style="text-align:left">=INDEX(<font color="Blue">'Part Exchange Schedule'!$E$5:$E$450,MATCH(<font color="Red">Dashboard!B7,'Part Exchange Schedule'!$C$5:$C$450,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D7</th><td style="text-align:left">=COUNTIF(<font color="Blue">'Part Exchange Schedule'!$C$5:$C$200,Data!B5</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B8</th><td style="text-align:left">=Data!B6</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C8</th><td style="text-align:left">=INDEX(<font color="Blue">'Part Exchange Schedule'!$E$5:$E$450,MATCH(<font color="Red">Dashboard!B8,'Part Exchange Schedule'!$C$5:$C$450,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D8</th><td style="text-align:left">=COUNTIF(<font color="Blue">'Part Exchange Schedule'!$C$5:$C$200,Data!B6</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B9</th><td style="text-align:left">=Data!B7</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C9</th><td style="text-align:left">=INDEX(<font color="Blue">'Part Exchange Schedule'!$E$5:$E$450,MATCH(<font color="Red">Dashboard!B9,'Part Exchange Schedule'!$C$5:$C$450,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D9</th><td style="text-align:left">=COUNTIF(<font color="Blue">'Part Exchange Schedule'!$C$5:$C$200,Data!B7</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B10</th><td style="text-align:left">=Data!B8</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C10</th><td style="text-align:left">=INDEX(<font color="Blue">'Part Exchange Schedule'!$E$5:$E$450,MATCH(<font color="Red">Dashboard!B10,'Part Exchange Schedule'!$C$5:$C$450,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D10</th><td style="text-align:left">=COUNTIF(<font color="Blue">'Part Exchange Schedule'!$C$5:$C$200,Data!B8</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B11</th><td style="text-align:left">=Data!B9</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C11</th><td style="text-align:left">=INDEX(<font color="Blue">'Part Exchange Schedule'!$E$5:$E$450,MATCH(<font color="Red">Dashboard!B11,'Part Exchange Schedule'!$C$5:$C$450,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D11</th><td style="text-align:left">=COUNTIF(<font color="Blue">'Part Exchange Schedule'!$C$5:$C$200,Data!B9</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B12</th><td style="text-align:left">=Data!B10</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C12</th><td style="text-align:left">=INDEX(<font color="Blue">'Part Exchange Schedule'!$E$5:$E$450,MATCH(<font color="Red">Dashboard!B12,'Part Exchange Schedule'!$C$5:$C$450,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D12</th><td style="text-align:left">=COUNTIF(<font color="Blue">'Part Exchange Schedule'!$C$5:$C$200,Data!B10</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B13</th><td style="text-align:left">=Data!B11</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C13</th><td style="text-align:left">=INDEX(<font color="Blue">'Part Exchange Schedule'!$E$5:$E$450,MATCH(<font color="Red">Dashboard!B13,'Part Exchange Schedule'!$C$5:$C$450,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D13</th><td style="text-align:left">=COUNTIF(<font color="Blue">'Part Exchange Schedule'!$C$5:$C$200,Data!B11</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H7</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$C$7:$C$500=G7</font>)*$D$7:$D$500</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H8</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$C$7:$C$500=G8</font>)*$D$7:$D$500</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H9</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$C$7:$C$500=G9</font>)*$D$7:$D$500</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H10</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$C$7:$C$500=G10</font>)*$D$7:$D$500</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H11</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$C$7:$C$500=G11</font>)*$D$7:$D$500</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H12</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$C$7:$C$500=G12</font>)*$D$7:$D$500</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H13</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$C$7:$C$500=G13</font>)*$D$7:$D$500</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Jonmo1]

Adjust the formula in column C to return Blank instead of #N/A

Try this in C7 and filled down.

=IF(ISNA(MATCH(Dashboard!B7,'Part Exchange Schedule'!$C$5:$C$450,0)),"",INDEX('Part Exchange Schedule'!$E$5:$E$450,MATCH(Dashboard!B7,'Part Exchange Schedule'!$C$5:$C$450,0)))

 

[Joneye]

so putting in isna is like the isblank function but for errors?

 

[Jonmo1]

so putting in isna is like the isblank function but for errors?

No, it tests a reference for the #N/A error.
Returns true if the reference is an #N/A error, false if not.

 

[Joneye]

Brilliant many thanks for your help.