SUMPRODUCT not working, some cells with #N/A
 

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

SUMPRODUCT not working, some cells with #N/A
 

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

SUMPRODUCT not working, some cells with #N/A
 

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

SUMPRODUCT not working, some cells with #N/A
 

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