You're welcome!

--
Biff
Microsoft Excel MVP


"Ashish Mathur" wrote in message
...
Thank you.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"T. Valko" wrote in message
...
Unfortunately, SUMPRODUCT doesn't accept 3d references either!

However, we can still use SUMPRODUCT for multiple conditions across
multiple sheets but it becomes fairly complicated and the resulting
formula is "expensive" calculation-wise.

Create this defined name

Rng
Refers to:
=ROW(INDIRECT("4:8"))

This creates a vertical array of the numbers 4:8 that correspond to the
actual range references. This is used in the OFFSET function.

And the formula:

=SUMPRODUCT(--(T(OFFSET(INDIRECT("'Sheet"&{1,2,3}&"'!C4:C8"),Rng-4,,))=C4),--(N(OFFSET(INDIRECT("'Sheet"&{1,2,3}&"'!D4:D8"),Rng-4,,))15),N(OFFSET(INDIRECT("'Sheet"&{1,2,3}&"'!D4 :D8"),Rng-4,,)))

Note that the sheet sequence *must* be a horizontal array: {1,2,3}. If
you had 50 sheets you wouldn't want to do this:
{1,2,3,4,5,6,7,8,9,10,......50}. You could do this:

COLUMN(INDIRECT("A:AX"))

Using COLUMN makes it a horizontal array.

This is also made somewhat easier since the sheet names follow a
sequential naming pattern. If they didn't then you'd have to list the
sheet names in a horizontal range of cells and then refer to that range.

Note the use of the T and N functions. Without those functions this
wouldn't work. We use T in the first array because we're testing that
range for the TEXT entry Z held in C4. WE use N in the other arrays
because we're testing those arrays for NUMBERS.

Rng-4

We need to calculate an array of offsets used in the OFFSET function that
equate to:

offset C4 and D4 by 0 rows
................................1 row
................................2 rows
................................3 rows

It would be the same as:

Rng-MIN(ROW(Rng))

If "it" gets much more complicated than this I would suggest using
intermediate formulas on each sheet and then summing those cells.



exp101
--
Biff
Microsoft Excel MVP


"Ashish Mathur" wrote in message
...
Hi,

I am trying to adapt your solution to do a SUMIF() based on multiple
conditions across sheets. I am basically trying to sum column B of the
3 sheets based on 2 conditions - column A should have Z and column B
should have a number greater than 15. Cell C4 in the formula below
holds Z.

=SUMPRODUCT(SUM((INDIRECT("Sheet"&ROW(INDIRECT("1: 3"))&"!C4:C8")=C4)*(INDIRECT("Sheet"&ROW(INDIRECT( "1:3"))&"!D4:D8")15)*(INDIRECT("Sheet"&ROW(INDIRE CT("1:3"))&"!D4:D8"))))

This formula sums up the the values on the first sheet only. Where am I
going wrong?

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"T. Valko" wrote in message
...
=SUM('R5:R10'!AV5:AW11)/COUNTIF('R5:R10'!AV5:AW11,"0")

COUNTIF doesn't accept 3d references.

Try one of these

=SUM('R5:R10'!AV5:AW11)/SUMPRODUCT(COUNTIF(INDIRECT("'R"&{5,6,7,8,9,10}&"' !AV5:AW11"),"0"))

=SUM('R5:R10'!AV5:AW11)/SUMPRODUCT(COUNTIF(INDIRECT("'R"&ROW(INDIRECT("5:1 0"))&"'!AV5:AW11"),"0"))

--
Biff
Microsoft Excel MVP


"Brandy" wrote in message
...
Hello All,

I have a 50 worksheet file that has a summary on each page that I
would like
to summarize again on a summary page into broader categories. The
summary
value is a formula in a merged cell and right now I have
=SUM('R5:R10'!AV5:AW11)/COUNTIF('R5:R10'!AV5:AW11,"0"). The problem
is when
I add in values it doesn't change the number for me.

Any idea what I am doing wrong?

Any help would be greatly appreciated.

Thanks,

B