Put all the sheet names in a list, M1,M2,M3 etc.

=SUMPRODUCT(SUMIF(INDIRECT(M1:Mn&"!A1"),"<#VALUE! "))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Ray" wrote in message
oups.com...
Hi,

I need to consolidate one cell from a large number of sheets .... on a
few of the sheets, the value of this cell is #N/A (by design). This
'error' has to remain, as it's used in a chart. I know that I could
'solve' the problem by adding another column to each of the individual
sheets, and using one for the 'calculation' and the other for
charting -- and then consolidating the 'calculation' column. I don't
want to do this for memory/file size issues.

I've tried using an array formula (entered with CTRL-SHIFT-ENTER) but
it hasn't worked ... now I get a #VALUE! error. What am I doing
wrong? Here's the array formula that hasn't been working:
{=SUMPRODUCT(IF(ISERROR('IFS152 Trend:IFS413 Trend'!K82),"",'IFS152
Trend:IFS413 Trend'!K82)*('IFS152 Trend:IFS413 Trend'!K82))}

Hopefully, it's something really obvious that an MVP will pick up
right away ...

TIA,
ray