AVERAGEIF for Multiple Sheets

[Geshwinn]

Hi guys,

I have more than 10 sheets with data like this:

1. Apples 5
2. Pears 34
3. Appricots 45
4. Watermelons 7


I want to have in a different sheet the averages of each item from all sheets. I tried to do it with a formula but it becomes really big and I didn't like it.

Is there a way to do it with a loop?

Thanks!

 

[Ganjin]

hmm, assuming that Fruit is in Column A for all Sheets
and, assuming that Number is in Column B for all Sheets,

then, for the Average Sheet:
<TABLE style="WIDTH: 117pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=156 border=0><COLGROUP><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 69pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=92 height=20>Apples</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=64>10</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Pears</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>15</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Appricots</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>20</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Watermelons</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>25</TD></TR></TBODY></TABLE>
the formula in B1
=AVERAGE(SUMIF(INDIRECT("Sheet"&ROW(INDIRECT("1:10"))&"!A:A"),A1,INDIRECT("Sheet"&ROW(INDIRECT("1:10"))&"!B:B")))
Ctrl+Shift+Enter

 

[Geshwinn]

The thing is that they are not always in the same columns. In one sheet they are in columns X and Y, in the other P and R.

This is why I didn't want to use a function because it will become too long.

 

[Ganjin]

hmm, assuming that Fruit is in Column A for all Sheets
and, assuming that Number is in Column B for all Sheets,

then, for the Average Sheet:
<table style="width: 117pt; border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="156"><colgroup><col style="width: 69pt;" width="92"><col style="width: 48pt;" width="64"></colgroup><tbody><tr style="height: 15pt;" height="20"><td class="xl63" style="border: 0.5pt solid windowtext; width: 69pt; height: 15pt; background-color: transparent;" width="92" height="20">Apples</td><td class="xl63" style="border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext; width: 48pt; background-color: transparent;" align="right" width="64">10</td></tr><tr style="height: 15pt;" height="20"><td class="xl63" style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; height: 15pt; background-color: transparent;" height="20">Pears</td><td class="xl63" style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" align="right">15</td></tr><tr style="height: 15pt;" height="20"><td class="xl63" style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; height: 15pt; background-color: transparent;" height="20">Appricots</td><td class="xl63" style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" align="right">20</td></tr><tr style="height: 15pt;" height="20"><td class="xl63" style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; height: 15pt; background-color: transparent;" height="20">Watermelons</td><td class="xl63" style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" align="right">25</td></tr></tbody></table>
the formula in B1
=AVERAGE(SUMIF(INDIRECT("Sheet"&ROW(INDIRECT("1:10"))&"!A:A"),A1,INDIRECT("Sheet"&ROW(INDIRECT("1:10"))&"!B:B")))
Ctrl+Shift+Enter

hmm, although the formula (updated below) ...

=SUM(SUMIF(INDIRECT("Sheet"&ROW(INDIRECT("1:10"))&"!A:AX"),
A1,OFFSET(INDIRECT("Sheet"&ROW(INDIRECT("1:10"))&"!A:AX"),0,1)))/
SUM(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT("1:10"))&"!A:AX"),"APPLES"))
Ctrl+Shift+Enter (adjust A:AX if not large enough, assuming fruit and number are right next to each other there should be no problem)

... is quite long, I doubt that there is (excluding VBA) an easier way to achieve what you want.

anywho, best of luck

 

[Ganjin]

woops, slight change:
=SUM(SUMIF(INDIRECT("Sheet"&ROW(INDIRECT("1:10"))&"!A:AX"),
A1,OFFSET(INDIRECT("Sheet"&ROW(INDIRECT("1:10"))&"!A:AX"),0,1)))/
SUM(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT("1:10"))&"!A:AX"),A1))
Ctrl+Shift+Enter

 

[Geshwinn]

Thanks!

If I wanted to do it with VBA what should the code be like? This is what I meant by a "loop".