SUM COLLECT Multiple referenced columns

Michelle Maas

I want to automate data on a metrics sheet from a referenced income and expenditure calculating sheet.

On the referenced sheet I have the project names listed under sections which group them into the relevant project type, calculating under the relevant month and totalling in the section Parent row.

Each month I delete the previous month column and currently I am remapping the ranges from the referenced sheet to the metric sheet's relevant cell. This is very time consuming. I thought if I could create a formula which recognises the relevant column months, then all I would need to do is change the reference column link and numbers, and roll it down to the different metric rows. I have been trying the below formula but it is not working, plus I need to add more month columns depending on which metric section it is referring to.

=SUM(COLLECT({Total Income and Expenditure 22/04}, {Total Income and Expenditure Name}, "Sales Orders - Service & Maintenance")

I am not aware of other more efficient ways to automate this further so any ideas are welcomed!

Jeff Reisman

@Michelle Maas

Let me share a solution I built that did something similar. This may help you automate your solution.

I built a system for tracking Bills of Lading in our warehouse, and the supervisors wanted a metric sheet showing how many BOLs were handled daily by each employee over the immediate past 10 work days.

I structured my metric sheet with a column for the employee names, and then 10 columns, one for each of the past 10 workdays. Each day, the date referenced by each column shifts by one work day. There's also a column for the 10-day total. (The system was retired a few weeks ago following implementation of an actual inventory/order mgmt system, hence the zeros!)

So how do I get the dates to shift and the formulas to pull the right counts every day? Following these visible columns I have 11 hidden columns - 10 that pull the workday dates and one that lists company holiday dates. I used the WORKDAY function to obtain the date for the workday date I want in that column. So in the "Day1" column:

=WORKDAY(TODAY(0), -1, Holidays:Holidays)

Give me the workday date that is one day before today, excluding holidays listed in the holidays column.

Repeat for the other 9 columns, just increasing the number of days back:

=WORKDAY(TODAY(0), -2, Holidays:Holidays)

Back in my visible columns, I use a COUNTIFS to pull the count of assigned BOLs from my tracking sheet for that row's employee and that day's date:

=COUNTIFS({BOL Active Tracking Created}, [Day1]@row, {BOL Active Tracking Assigned Name}, [Assigned To Name]@row) + ""

(I'm converting the number value to text by adding the + "" at the end. I think I did this because the columns are Date-type columns, probably because at the time I built this I didn't know how to create a "pretty" date in text format using a formula)

Looking back at it now, I probably could have incorporated the formulas in my hidden columns into the COUNTIFS as well. =COUNTIFS({BOL Active Tracking Created}, (WORKDAY(TODAY(0), -1, Holidays:Holidays), {BOL Active Tracking Assigned Name}, [Assigned To Name]@row) + ""

So how does this help you? Well, the data in the "1 Day Ago" column is always the data from the workday immediately preceding the current day. So, if you collect your metric data into the same column each month, your references wouldn't need to change at all. The column for the current month would always be for the current month, the column for last month will always have last month's metrics, etc.

So in place of a column called 22/04, that would be called Current Month, 22/03 would be One Month Back, 22/05 would be One Month Ahead, etc. You can add a header row that displays the name of the current month, last month, etc via formula. Just set up a lookup list somewhere (like a Month Name sheet) listing the month name that goes with each month number (i.e. MonthNum column value 1 = MonthName column value January, 2 is February)

=(INDEX({Month Name Sheet MonthName range}, MATCH(MONTH(TODAY(0)), {Month name Sheet MonthNum range}, 0))

Then for the data going those columns, your either enter it manually (and manually copy from column to column each month) or if using formulas, use formulas that use the MONTH function to reference the current month, next month, the month after that, etc.

Then your metrics sheet would reference the same columns for each look ahead section. (i.e. the formulas in the 3 Month Look Ahead section reference the One Month Ahead, Two Months Ahead, and Three Month Ahead columns, whose values update automatically every month.