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.

These are the sales on Oct-2.

Sales in Oct-3

Dataset for Oct-4.

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

Sales in Oct-6.

Here, the values for Oct-7.

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.

• After that, select the Oct-1 worksheet.

• Select cell C6 in the Oct-1 worksheet.

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

• Then press the Enter key.

• 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.

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.

• 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.

• Enter the following formula.

• Press the Enter key.

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

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.