Hello Azri,

As per your setup I would use an array formula to sum the different criterias from the different sheets.
In your Summary sheet, paste in below formula in cell B2, press and hold the keys CTRL + SHIFT and then press ENTER. Lastly, copy cell B2 down by clicking the lower right corner of cell B2 and drag down to cell B5.

=SUM((Sheet1!$C$2:$C$9+Sheet2!$C$2:$C$9+Sheet3!$C$2:$C$9)*(Sheet1!$A$2:$A$9=summary!$A2)*(Sheet1!$B$2:$B$9="Actual"))

Even though your approach works fine there are better ways to set up your data. Do check this blog article as a reference.

Br,
Anders

Anders Sehlstedt said
Hello Azri,

As per your setup I would use an array formula to sum the different criterias from the different sheets.
In your Summary sheet, paste in below formula in cell B2, press and hold the keys CTRL + SHIFT and then press ENTER. Lastly, copy cell B2 down by clicking the lower right corner of cell B2 and drag down to cell B5.

=SUM((Sheet1!$C$2:$C$9+Sheet2!$C$2:$C$9+Sheet3!$C$2:$C$9)*(Sheet1!$A$2:$A$9=summary!$A2)*(Sheet1!$B$2:$B$9="Actual"))

Even though your approach works fine there are better ways to set up your data. Do check this blog article as a reference.

Br,
Anders  

Thanks a lot mr Anderas.. array formula that you provide is working.. I though that formula could use on my real worksheet that contain 12 sheets, just add another 7 sheet at first array right?

Hello Azri,

Thank you for your feedback, always nice to know that a problem was solved.

Yes, you can just add the extra sheets in the first section of the array formula.
But yet again, even if this works, it is not the best solution.

Have a nice day ahead!

Br,
Anders

Hi Azri

If your worksheets all have the same structure, perhaps you can try using 3-D summing.

For example =SUM(Sheet1:Sheet3!C3) etc. The formula is shorter and if you need to add additional sheets, just add them between the 1st and last sheet and the formulas will work without any modifications.

Hope this helps.

Sunny

SunnyKow said
Hi Azri

If your worksheets all have the same structure, perhaps you can try using 3-D summing.

For example =SUM(Sheet1:Sheet3!C3) etc. The formula is shorter and if you need to add additional sheets, just add them between the 1st and last sheet and the formulas will work without any modifications.

Hope this helps.

Sunny  

Hai sunny.. had been try that you suggest, but getting error at the end. I'm really not sure how does you make it. kindly give me an full formula function as my reference. TQ

Hi Azri

To create a formula with a 3-D reference in the argument, perform the following steps:

1. Click the cell where you want to enter your 3D formula.
2. Type the equal sign (=), enter the function's name, and type an opening parenthesis, e.g. =SUM(
3. Click the tab of the first worksheet that you want to include in a 3D reference.
4. While holding the Shift key, click the tab of the last worksheet to be included in your 3D reference.
5. Select the cell or range of cells that you want to calculate.
6. Press the Enter key to complete your Excel 3-D formula.

Hope this helps

Sunny

SunnyKow said
Hi Azri

To create a formula with a 3-D reference in the argument, perform the following steps:

1. 1. Click the cell where you want to enter your 3D formula.

1. 1. Type the equal sign (=), enter the function's name, and type an opening parenthesis, e.g. =SUM(

1. 1. Click the tab of the first worksheet that you want to include in a 3D reference.

1. 1. While holding the Shift key, click the tab of the last worksheet to be included in your 3D reference.

1. 1. Select the cell or range of cells that you want to calculate.

1. 1. Press the Enter key to complete your Excel 3-D formula.

Hope this helps

Sunny  

Hai sunny.. I'm totally lost at step no 5.. 

Cant get what you mind.. 

Hi Azri

See my example (with instructions included)

Good luck

Sunny

SunnyKow said
Hi Azri

See my example (with instructions included)

Good luck

Sunny  

Woowww... work great like honda engine.. 

Thanks sunny.. now I'm understand.. m/