Formula for the cells on the same address, but on the different sheets

Let us have several identical tables that are on different Excel worksheets, but always on the same address. How to make SUM or AVERAGE of these tables?Table on the Sheet1

Table on the Sheet2

Table on the Sheet3

Table on the Sheet4
I will create SUM and AVERAGE of these tables to the new Sheet5. The formulas for the first item (Apples) are



This formulas include all of the cells B2, which are on the Sheet1, Sheet2, Sheet3 and Sheet4.

If the sheets were named according to months, the formula would look like this:


Solution of the SUM function:

SUM with formulas

SUM with numbers

Solution of the AVERAGE function:

AVERAGE with formulas

AVERAGE with numbers

Important: It depends on the sheets order in the workbook. If it is like on the following picture, the formula will include only Sheet1, Sheet2 and Sheet4. Excel takes into account the order of the sheets and not their names.

Sheets order


Add a comment »One comment to this article

  1. This solution helped me. I did sum over multiple sheets and it worked! Can you provide some deep explanation of this array formula? Thank you.


Copyright © All Rights Reserved · Green Hope Theme by Sivan & schiy · Proudly powered by WordPress