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
 10.7K Get Help
 63 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!