SUMIF over 3-D reference

Posted by Michael Stanton on March 30, 2001 1:16 PM

Hi,

I am trying to use SUMIF with a 3-D reference to a cell
range but it isn't working. The formula, on Sheet3, is:

=SUMIF(Sheet1:Sheet2!A:A, B1, Sheet1:Sheet2!A:A)

It gives me a #VALUE! error.
Does anyone know how to make this work?
Thanks, Mike

Posted by Tim Francis-Wright on March 30, 2001 2:00 PM

The Excel help files explain that:

"You can use 3-D references to refer to cells on other sheets, to define names, and to create formulas by using the following functions: SUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV, STDEVA, STDEVP, STDEVPA, VAR, VARA, VARP, and VARPA."

You can't use 3-D references in array formulas
or with the intersection operator.

If you only have two sheets that require the SUMIF,
you could use
=SUMIF(Sheet1!A:A, B1, Sheet1!A:A)+SUMIF(Sheet2!A:A, B1, Sheet2!A:A)

or, you could set up the cells in column C on each sheet so that they looked like:
C1: =IF(A1>2000,B1,0)

You can hide column C if you need to.

then you could use
=SUM(Sheet1:Sheet2!C:C)

Hope that helped.

, A and B