Average columns across multiple sheets

**Stayfair** · 10-04-2008, 10:34 AM

I have 10 sheets in my workbook that represent 10 employees. Column G, rows 5 through 739, represent the amount of time each employee spends doing a certain task for each day of the year. All these cells have zeros in them until a we come to that day and a time is entered for that day. The result in each cell is a certain amount of minutes spent on that task.

I can average each employees time individually on it's own sheet with this formula: {=AVERAGE(IF(G5:G739=0,"",G5:G739))}, but what I'm having a problem doing is averaging all 10 employees together on a separate sheet. I'm trying to figure out company wide how much time is spent on this particular task each day.

My depth of understanding here isn't too great, I'm learning as I go. I think I have to incorporate that formula while also selecting each sheet. I'm starting to get the BSOD in my head with this one.

Hopefully I've given enough info that someone can help me through this. Probably a basic problem for most of you, unfortunately, not for me.

Thanks.

**Domenic** · 10-04-2008, 12:53 PM

Probably best to do something like this...

1) In the same cell for each sheet, let's say M2, enter the following formula...

=SUM(G2:G739)

2) In the same cell for each sheet, let's say N2, enter the following formula...

=COUNTIF(G2:G739,">0")

3) Then for the average, try...

=SUM('Sheet1:Sheet10'!M2)/SUM('Sheet1:Sheet10'!N2)

Otherwise, for a single formula solution, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=AVERAGE(IF(N(OFFSET(INDIRECT("'"&A2:J2&"'!G5:G739"),ROW(INDIRECT("5:739"))-5,0,1))>0,N(OFFSET(INDIRECT("'"&A2:J2&"'!G5:G739"),ROW(INDIRECT("5:739"))-5,0,1))))

...where A2:J2 contains the sheet names. However, this formula is neither efficient nor robust.

Hope this helps!

**Stayfair** · 10-04-2008, 01:40 PM

I'll give this a shot as soon as I get the chance later today. Thanks a bunch.

Originally Posted by Domenic

Probably best to do something like this...

1) In the same cell for each sheet, let's say M2, enter the following formula...

=SUM(G2:G739)

2) In the same cell for each sheet, let's say N2, enter the following formula...

=COUNTIF(G2:G739,">0")

3) Then for the average, try...

=SUM('Sheet1:Sheet10'!M2)/SUM('Sheet1:Sheet10'!N2)

Otherwise, for a single formula solution, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=AVERAGE(IF(N(OFFSET(INDIRECT("'"&A2:J2&"'!G5:G739"),ROW(INDIRECT("5:739"))-5,0,1))>0,N(OFFSET(INDIRECT("'"&A2:J2&"'!G5:G739"),ROW(INDIRECT("5:739"))-5,0,1))))

...where A2:J2 contains the sheet names. However, this formula is neither efficient nor robust.

Hope this helps!

**Stayfair** · 10-04-2008, 03:05 PM

It all worked beautifully, thanks again Domenic.

Tom

**junyi.ho** · 02-10-2011, 09:10 PM

I have been searching the whole forum but unable to find any solution that fits what i need

I have 2 sheets of data.

I would like to search data in sheet 2 and data in sheet 1. If the data is not the same in any of the rows, it will be extracted out in sheet 3.

An example will be

Sheet1:
A B C
MS 2973 Vijeyacone Ariyacone

Sheet2
A B C
J5 2973 VIJEYACONE ARIYACONE

in this situation, sheet 1 column B and C are the same but Column A is different hence on sheet3,

Sheet3
MS 2973 Vijeyacone Ariyacone

I have attached a spread sheet which will show a more detailed table of what i want to achieve. Sheet1 and 2 is the raw data and Sheet3 will show the result I want.

Would appreciate if someone is able to solve this Thank You.