SUMPRODUCT not working, some cells with #N/A

  • Thread starter Thread starter Ray
  • Start date Start date
R

Ray

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)
 
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
 
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
 
Back
Top