Need help with Sum by month
I am working on creating a summary sheet to show totals by the monthly forecast. Not sure how to connect the data correctly to this sheet.
Source smartsheet
Thanks in advance
Best Answer

Thanks. Here's an example of how to do this from one sheet to another.
First, you're going to want to add a column on sheet 2 which takes the date and lets us know what month it falls in. Like so:
Month formula:
=MONTH([SAP ETA]@row)
Then on the first sheet you can fill it out like so. Since you're using parent/child rows, you'll want to use cell formulas instead of column formulas. That means each time you add a new Customer Account you'll want to either copypaste the formula from the cell above or drag it down with the little box in the corner of the cell above. Here's sheet 1 finished:
The cell formula will look something like this for January:
=SUMIFS({Quantity (kg) Sheet 2}, {Plant Sheet 2}, =[Account Name]@row, {Month Sheet 2}, =1)
For the following months, you just change the number at the end to coincide with the number month. 2 for February, 3 for March, 4 for April, etc.
Then in the January for the Customer Accounts row, you can use a formula =SUM(CHILDREN()) to get a grand total of all of the accounts for January.
Answers

Where would the data go on the first sheet? It looks like the first sheet has a monetary value (dollars $) and the second sheet has a weight in kilos. I'm not sure how you'd like to match things up onto the first sheet without some additional information.

@Mike TV Yes, good catch. This should be in kilos. I have fixed the sheet at my end.

Thanks. Here's an example of how to do this from one sheet to another.
First, you're going to want to add a column on sheet 2 which takes the date and lets us know what month it falls in. Like so:
Month formula:
=MONTH([SAP ETA]@row)
Then on the first sheet you can fill it out like so. Since you're using parent/child rows, you'll want to use cell formulas instead of column formulas. That means each time you add a new Customer Account you'll want to either copypaste the formula from the cell above or drag it down with the little box in the corner of the cell above. Here's sheet 1 finished:
The cell formula will look something like this for January:
=SUMIFS({Quantity (kg) Sheet 2}, {Plant Sheet 2}, =[Account Name]@row, {Month Sheet 2}, =1)
For the following months, you just change the number at the end to coincide with the number month. 2 for February, 3 for March, 4 for April, etc.
Then in the January for the Customer Accounts row, you can use a formula =SUM(CHILDREN()) to get a grand total of all of the accounts for January.

@Mike TV Thanks a lot. It works perfectly :)
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.5K Get Help
 367 Global Discussions
 202 Industry Talk
 432 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!