# 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. 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 copy-paste 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.

• ✭✭✭✭✭✭

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.

• ✭✭✭✭
edited 05/19/22

@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 copy-paste 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!