SUM/COUNTIF across multiple worksheets

[Brandy]

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

 

[T. Valko]

=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:10"))&"'!AV5:AW11"),">0"))

 

[Ashish Mathur]

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"))&"!D48")>15)*(INDIRECT("Sheet"&ROW(INDIRECT("1:3"))&"!D48"))))

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]

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}&"'!D48"),Rng-4,,))>15),N(OFFSET(INDIRECT("'Sheet"&{1,2,3}&"'!D48"),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

 

[Ashish Mathur]

Thank you.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Thank you.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Herbert Seidenberg]

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

 

[Lori Miller]

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)