Need help with Sum by month

Kashmala Zaman

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

Mike TV

@Kashmala Zaman

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

@Kashmala Zaman

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.

Kashmala Zaman

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

Mike TV

@Kashmala Zaman

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.

Kashmala Zaman

@Mike TVThanks a lot. It works perfectly :)