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

=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

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

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

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

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

Ashish:
Excel 2007 Pivot Table
Sum multiple tabs with criteria.
No code, no formulas.
http://www.mediafire.com/file/ynxrxz...03_21_09a.xlsx

Instead of COUNTIF('R5:R10'!AV5:AW11,"0") use:

=INDEX(FREQUENCY('R5:R10'!AV5:AW11,0),2)

with numeric data you can use other functions that are enabled for
multiple-sheet references. eg Instead of =SUMIF('*'!A1,"0") use:

=AVEDEV('*'!A1,-SUM('*'!A1))*COUNT('*'!A1,0)/2+MIN(SUM('*'!A1),0)


"Brandy" wrote:

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