Log In
Register
Home
March 26, 2018
Offline
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..
See Answer
VIP
Trusted Members
December 7, 2016
Offline
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
Offline
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
Offline
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
VIP
Trusted Members
June 25, 2016
Offline
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
Offline
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
Offline
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
Offline
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)
