Summing values from multiple workbooks all in the same format

Hi there,

In the attached example, I have 4 x workbooks, with the first workbook 'Summary' an exact copy of the other 3 x workbooks (Room1, Room2 & Room3) that contain the values I would like to collect and sum in the 'Summary' workbook. I have highlighted in cell C11 the value to be returned, which is simply the total of same cell C11 value in the 3 x workbooks (Room1, Room2 & Room3). This value is then divided by 3 to achieve an average score. This approach would apply to all cells in column C of the Summary.

As I have 50+ workbooks I'm looking for a formula to assist this process.

Any help would be gratefully received.

Ben

If you have all your sheets in one file, like you have done for your sample, then you can use this formula:

=SUM(Room1:Room3!C11)/COUNT(Room1:Room3!C11)

or indeed:

=AVERAGE(Room1:Room3!C11)

So, if you did this for all your sheets the formula would be:

=AVERAGE(Room1:Room50!C11)

Hope this helps.

Pete

Thanks. Very helpful and works well.

Ben

You're welcome. Thanks for the rep.

Pete