

March 26, 2018

Hai guys.. Need help on how to sum the Sale (Actual) using combination of excel formula SUMPRODUCT+SUMIFS+INDIRECT.
The "Actual" Sale need to be summarize on to sheet named 'summary', data is obtained from sheet1 to sheet3.
Hopefuly some one colud give me functional formula on to my attached sample workbook.
Thanks..

VIP

Trusted Members

December 7, 2016

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

Answers Post


March 26, 2018

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?

VIP

Trusted Members

December 7, 2016


VIP

Trusted Members

June 25, 2016

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


March 26, 2018

SunnyKow said
Hi AzriIf 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

VIP

Trusted Members

June 25, 2016

Hi Azri
To create a formula with a 3-D reference in the argument, perform the following steps:
- Click the cell where you want to enter your 3D formula.
- Type the equal sign (=), enter the function's name, and type an opening parenthesis, e.g. =SUM(
- Click the tab of the first worksheet that you want to include in a 3D reference.
- While holding the Shift key, click the tab of the last worksheet to be included in your 3D reference.
- Select the cell or range of cells that you want to calculate.
- Press the Enter key to complete your Excel 3-D formula.
Hope this helps
Sunny


March 26, 2018

SunnyKow said
Hi AzriTo create a formula with a 3-D reference in the argument, perform the following steps:
- Click the cell where you want to enter your 3D formula.
- Type the equal sign (=), enter the function's name, and type an opening parenthesis, e.g. =SUM(
- Click the tab of the first worksheet that you want to include in a 3D reference.
- While holding the Shift key, click the tab of the last worksheet to be included in your 3D reference.
- Select the cell or range of cells that you want to calculate.
- 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..
1 Guest(s)
