SUM/COUNTIF across multiple worksheets
=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
|