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