Finding an average across multiple worksheets in the same workbook

Hi all,

I'm trying to get averages for different values across a years worth of worksheets (Jan-Dec). Each worksheet contains an account number (column A has various accounts such as "shipping" and "cost of sales"). The total for these values is located in column D.

Some of the account numbers are the same across all 12 worksheets, but with different totals, of which I'm trying to average. Basically what I'd like the formula on my "Averages" worksheet to do is to look across the twelve worksheets, find the total values for each account, and average them out. So if January has "Shipping" in column A, I want the average total of all the worksheets that contain "Shipping", keeping in mind that maybe only 8 of the 12 worksheets have "Shipping" in column A.

On that same note, there are sub accounts in column B that also have totals...i.e. Under the head account of "Shipping" there might be a "Foreign" sub account, which also has a total that I'd like to average, but keep them all separated by the head account.

I kind of rambled, but I gotta think there's an easy way to to this...any help would be greatly appreciated!!

Thanks!

I've attached a pic of what the spreadsheet looks like...each month has similar data, but not everything is the same or included on each worksheet.

Hi Dave,

Thanks...that would work, but I need the results to be specific to the header title, which isn't in the same location in column A on each sheet.

I need something like "look for value XX in column A of all 12 sheets, and find the average of the value in column D in the same row as value XX", and so on. Is that possible?

I'm afraid you'll need some VBA here.
For the future have a look at these tips for proper database design http://www.ozgrid.com/forum/announcement.php?f=46&a=6
Far less headaches

Good luck !

Originally Posted by GYG

Hi Dave,

Thanks...that would work, but I need the results to be specific to the header title, which isn't in the same location in column A on each sheet.

I need something like "look for value XX in column A of all 12 sheets, and find the average of the value in column D in the same row as value XX", and so on. Is that possible?

List your worksheets tabs names in column C starting with cell C3 in your average worksheet and input formula in cell D3.


=SUMPRODUCT(SUMIF(INDIRECT("'"&C3:C14&"'!A4:A100"),D3,INDIRECT("'"&C3:C14&"'!D4:D100")))/SUMPRODUCT((T(OFFSET(INDIRECT("'"&C3:C14&"'!A4:A100"),COLUMN(INDIRECT("4:30"))-4,0,1,1))=D3)*(N(OFFSET(INDIRECT("'"&C3:C14&"'!D4:D100"),COLUMN(INDIRECT("4:30"))-4,0,1,1))<>""))


Hope it helps!

Originally Posted by vane0326

List your worksheets tabs names in column C starting with cell C3 in your average worksheet and input formula in cell D3.


=SUMPRODUCT(SUMIF(INDIRECT("'"&C3:C14&"'!A4:A100"),D3,INDIRECT("'"&C3:C14&"'!D4:D100")))/SUMPRODUCT((T(OFFSET(INDIRECT("'"&C3:C14&"'!A4:A100"),COLUMN(INDIRECT("4:30"))-4,0,1,1))=D3)*(N(OFFSET(INDIRECT("'"&C3:C14&"'!D4:D100"),COLUMN(INDIRECT("4:30"))-4,0,1,1))<>""))


Hope it helps!

Hmmm...it's telling me there's a circular reference...pic attached...

Originally Posted by GYG

Hmmm...it's telling me there's a circular reference...pic attached...

Sorry about that. Try...


=SUMPRODUCT(SUMIF(INDIRECT("'"&C3:C14&"'!A4:A100"),"Shipping",INDIRECT("'"&C3:C14&"'!D4:D100")))/SUMPRODUCT((T(OFFSET(INDIRECT("'"&C3:C14&"'!A4:A10 0"),COLUMN(INDIRECT("4:30"))-4,0,1,1))="Shipping")*(N(OFFSET(INDIRECT("'"&C3:C14&"'!D4: D100"),COLUMN(INDIRECT("4:30"))-4,0,1,1))<>""))


Hope it helps!