Autosum won't work

[Christine Wilso]

Does anyone know why autosum does not work in the following instance?

I have a column which reads info from another spreadsheet using th
following array formula
{=INDEX('[LPT Target Crimes Recorded Sharrow.xls]Shee
1'!E$6:E$81,MATCH(1,('[LPT Target Crimes Recorded Sharrow.xls]Shee
1'!$B$6:$B$81=$B9)*('[LPT Target Crimes Recorded Sharrow.xls]Shee
1'!$C$6:$C$81=$C9),0))}

For months which have not yet occurred #N/A appears. At the bottom o
this column I clicked on autosum and highlighted all the data includin
the #N/A , and the answer I get is #N/A . I thought it would ignor
these and just add up all the numbers

 

[Frank Kabel]

Hi
SUM won't ignore errors. One way: use the formula
=SUMIF(A1:A1000,"<>#NA")

or change your formula to
{=IF(ISNA(MATCH(1,('[LPT Target Crimes Recorded Sharrow.xls]Sheet
1'!$B$6:$B$81=$B9)*('[LPT Target Crimes Recorded Sharrow.xls]Sheet
1'!$C$6:$C$81=$C9),0)),"",INDEX('[LPT Target Crimes Recorded
Sharrow.xls]Sheet
1'!E$6:E$81,MATCH(1,('[LPT Target Crimes Recorded Sharrow.xls]Sheet
1'!$B$6:$B$81=$B9)*('[LPT Target Crimes Recorded Sharrow.xls]Sheet
1'!$C$6:$C$81=$C9),0)))}
--
Regards
Frank Kabel
Frankfurt, Germany

"Christine Wilso" <[email protected]>
schrieb im Newsbeitrag
news:[email protected]...