Automate SUM referenced multiple columns

Michelle Maas

I want to automate a metrics sheet (if possible), to reduce the manual effort of updating each month. I have one sheet that calculates the current month and future project income and expenditure by a formula in each relevant month's column, referencing the project sheet matching the account name and totalling in Parent rows relevant to the type of project.

From this I pull Metrics for those project group Parent rows. Each month I delete the past month column so this sheet is continuously rolling forward on the current month and looking 5 years ahead. Then I remap the reference range for each cell. This is very time consuming.

I have been playing with a SUMIF and/or SUM(COLLECT formula and seeing if I can add together several column totals in the relevant rows, and then all I would need to do is change the column month names in that formula i.e. {Total Income and Expenditure 22/04}. It either comes up with error notifications or 0. I am trying to avoid having to go in and remap ranges which takes longer than just changing the numbers. Any ideas?

Jeff Reisman

@Michelle Maas

To summarize what I answered in your other post -

The idea is to have your data move from column to column by itself based on the current month, rather than removing columns and re-creating references. You want one column that always contains the data for the current month, and another column that always has the data for one month ahead, another for two months ahead, etc.

To do this, use the various date functions to build the date values you're using to pull your data in for each month. Instead of the formulas containing a static date, you build the date value in each formula based off today's date.

For example, if I want to count values from a remote sheet with a start date in the month after the current month, I might use something like this:

=COUNTIFS({Remote Sheet Payment Type Column}, "Mortgage", {Remote Sheet Payment Date Column}, MONTH(@cell, (MONTH(TODAY())+1)))

This would always give me the count for rows with a payment date in the month after this month, whatever this month is.

Since you're going out 5 years, you'll want to include a criteria for YEAR as well:

=COUNTIFS({Remote Sheet Payment Type Column}, "Mortgage", {Remote Sheet Payment Date Column}, MONTH(@cell, (MONTH(TODAY())+1)), {Remote Sheet Payment Date Column}, IF(MONTH(TODAY()) < 12, YEAR(TODAY()), YEAR(TODAY())+1))

With the number of columns you'll have (60?) it might be beneficial to create a lookup sheet with rolling dates, doing all the date calculations going out 5 years, and referencing those lookup cells to match your date values in your formulas.

Michelle Maas

@Jeff Reisman this is great information and will be useful in future, so thank you for this. Unfortunately through looking at Smartsheet formulas, I realised the reason my formulas were not working is because the data is a sum value in parent rows. Smartsheet state for the cross-sheet formulas:

"Commonly used functions in cross-sheet formulas include: VLOOKUP, SUMIF, COUNTIF, MATCH, and INDEX. Functions that are not supported in references from another sheet are CHILDREN, PARENT, and ANCESTORS."