If you have data in the same cells in multiple sheets that need to be summed up together, you can do this using a very simple technique. And if you have a range of cells that you need to sum, you can sum one cell and then drag the formula down or right or copy it to those ranges, which need to be summed.
Let’s see how this technique works
Considering that we have a workbook that has 3 sheets – January, February, and March.
There are values in cells A2: A4 in each of these sheets.
Now in another sheet, we need to sum up the values in each of the cells. So we need to sum A2 in January, February, and March. Respectively for A3 and A4 in this example.
So in a blank sheet, type =SUM and then click on the January tab. Hold the shift key on the keyboard and select the 3rd sheet – March. Select the cell A2 and press Enter on your keyboard.
The formula will now show =SUM(January: March!A2)
You will get the sum of the cell A2 from January to March. Now drag down this formula to A4 and the values will be populated.
In this example, we had values only in A2: A4. However, if you have values horizontally, in columns B onwards, you can drag the formula right. Or copy the formula to other cells where you need to sum up the values from the corresponding sheets.
There is another method to sum the values from cells in different sheets. We can use the 3D reference option.
3D Reference: This option is used to combine the data from the different sheets in a single sheet. 3D reference contains three dimensions – rows, columns, and worksheets.
Let’s take an example, we have sales data for 3 months in 3 different sheets in an Excel workbook, and in one sheet, we will return the total quantity of all months agent wise.
Follow the below given steps to sum the values from the cells in different sheets -
If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at firstname.lastname@example.org
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.