Excel - SUM/COUNTIF across multiple worksheets

Asked By Brand on 20-Mar-09 04:50 PM
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 replied on 20-Mar-09 05:54 PM
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"))

--
Biff
Microsoft Excel MVP
Ashish Mathur replied on 20-Mar-09 10:55 PM
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(INDIRECT("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 replied on 21-Mar-09 01:12 AM
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

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 replied on 21-Mar-09 06:51 AM
Thank you.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
T. Valko replied on 21-Mar-09 11:52 AM
You're welcome!

--
Biff
Microsoft Excel MVP
Herbert Seidenberg replied on 21-Mar-09 06:54 PM
Ashish:
Excel 2007 Pivot Table
Sum multiple tabs with criteria.
No code, no formulas.
http://www.mediafire.com/file/ynxrxzdrklm/03_21_09a.xlsx
LoriMille replied on 22-Mar-09 03:50 AM
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)