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

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

Well, for one thing, you can't reference multiple sheets like that directly
in SUMPRODUCT.

I don't understand what the intent is. You seem to be multiplying the same
cells together:

K82*K82 from each sheet (although some may contain #N/A)

If that's really what you're trying to do you could sum the cells and then
just square the result. It's possible to sum across multiple sheets *and*
exclude the #N/A's.

Biff

"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

Biff, I'm simply trying to sum the value in K82 from many sheets. In
some cases, this value is #N/A, so I was attempting to use the array
formula to 'multiply' the number of TRUE values (K82<#N/A) with the
value in K82. But, it wasn't working out ...

Bob, I'll try your formula ....

thanks, ray