2 Ways to Use 3D SUMIF for Multiple Worksheets

Sometimes we have to summarise different data from multiple worksheets. Here in this article we will demonstrate two ways to use 3D SUMIF for multiple worksheets. Here we will use the SUM and SUMIF functions in these two different methods.


๐Ÿ“ Download Excel File


Learn to Use 3D SUMIF for Multiple Worksheets in Excel with These 2 Suitable Approaches

Here we will learn to use 3D SUM and SUMIF for multiple worksheets and we will also learn the difference between the two approaches. Here in this dataset, there are 7 worksheets that represent the daily sales of phones in a shop from October 1 to October 7.

Here are sales for Oct-1.

Dataset of 3D SUMIF for multiple worksheets-1

These are the sales on Oct-2.

Sales in Oct-3

Dataset of 3D SUMIF for multiple worksheets-2

Dataset for Oct-4.

Dataset of 3D SUMIF for multiple worksheets-3

These are the number of product sales on oct -5.

Sales in Oct-6.

Dataset of 3D SUMIF for multiple worksheets-4

Here, the values for Oct-7.


Approach

ย 1. Using SUM Function

Here in this method we will use the SUM function for 3D SUM in multiple worksheets and summarize the total sales of the shop in the last 7 days in the month of October.

โฌ‡๏ธโฌ‡๏ธ STEPS โฌ‡๏ธโฌ‡๏ธ

  • Firstly, select cell C6 in the SUM Summary.

  • Then enter the SUM function.

Using SUM function for 3D SUMIF in multiple worksheet

  • After that, select the Oct-1 worksheet.

Applying SUM function for 3D SUMIF in multiple worksheet

  • Select cell C6 in the Oct-1 worksheet.

  • Hold the Shift key and then select the Oct-7 worksheet.

Utilizing SUM function for 3D SUMIF in multiple worksheet

  • Then press the Enter key.
=SUM(โ€˜Oct-1:Oct-7โ€™!C6)

  • Select cell C6 and drag the Fill Handle to C10.

And here we got the total sales of each phone from Oct-1 to Oct-7. For example here the total sales of iPhone 14 from Oct-1 to Oct-7 were about 18000 USD.


Approach

2. Using SUMIF Function

In the second approach, we will use the SUMIF, INDEX, and SUMPRODUCT functions for multiple discontinuous worksheets.

For example, we want to sum up the sales of the products only for Oct-1, Oct-3, Oct-4, and Oct-6.

โฌ‡๏ธโฌ‡๏ธ STEPS โฌ‡๏ธโฌ‡๏ธ

  • For this, at first, we have to write down the names of the worksheets that we want to sum up.

Using SUMIF Function for multiple worksheet

  • Then we have to select the names of the worksheets and then go to the name box and name them as worksheets.

  • Now select cell C6 in the SUMIF summary worksheet.

Utilizing SUMIF function for multiple worksheet

  • Enter the following formula.
=SUMPRODUCT(SUMIF(INDIRECT(โ€œโ€˜โ€&worksheets&โ€โ€˜!โ€&โ€B6:B10โ€),B6,INDIRECT(โ€œโ€˜โ€&worksheets&โ€โ€˜!โ€&โ€C6:C10โ€)))

  • Press the Enter key.

  • Select cell C6 again and drag the Fill Handle from C6 to C10.

Applying SUMIF function for multiple worksheet

Here we got the sales of each phone during these 4 days only. For example, the total sales of Iphone 14 in Oct-1, Oct-3, Oct-4, and Oct-6 was 10500 USD.


๐Ÿ“ Takeaways from This Article

๐Ÿ“Œย  In this article we used SUM and SUMIF functions to sum from multiple worksheets.

๐Ÿ“Œย  The function is used for continuous worksheets and the SUMIF function works for continuous or discontinuous worksheets

๐Ÿ“Œย  We have also used other functions like INDEX and SUMPRODUCT functions.


Conclusion

In this article, we tried to show to every possible way to sum identical ranges from different worksheets. So you may the suitable method for you. If you have any questions, please leave them in the comments. Finally, we propose that you visit Excelden to discover more about Excel.

(Visited 24 times, 1 visits today)
Sakibul Hasan Nahid

Sakibul Hasan Nahid

Hello I am Sakibul Hasan Nahid. I have completed my BSc. in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo