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