Attempting to do an AVG using two identical sheets

Peggy Parchert

I recently had to move from one sheet to two for one of my project sheets. Both project sheets are identical in setup BUT one sheet is the last three years (2019 forward) and the other sheet contains data from beyond three years (2018 and later).

I've created a separate rollup sheet that I have been using but I can't seem to get my AVG formulas to work when I cross-sheet reference both sheets in the formula. I use this data in charts so I really need it to be all on one sheet (for all years).

Rollup sheet columns:

Year (text/number column)

AVG Duration (days) - Year ***this is where the formula I'm trying to get to work will go

_________________________

The two source sheet columns:

Sheet 1 Duration (days) (text/number column)

Sheet 1 Snowflake (checkbox column)

Sheet 1 Go-Live Date (date column)

___________________________

Sheet 2 Duration (days) (text/number column)

Sheet 2 Snowflake (checkbox column)

Sheet 2 Go-Live Date (date column)

___________________________

This formula worked before I had two sheets:

=IFERROR(AVG(COLLECT({Sheet 1 Duration (days)}, {Sheet 1 Snowflake}, 0, {Sheet 1 Go-Live Date}, IFERROR(YEAR(@cell), 0) = Year@row)), 0)

I attempted to just add Sheet 2 but that isn't working....

=IFERROR(AVG(COLLECT({Sheet 1Duration (days)}, {Sheet 1 Snowflake}, 0, {Sheet 1 Go-Live Date}, IFERROR(YEAR(@cell), 0) = Year@row)), 0), {Sheet 2 Duration (days)}, {Sheet 2 Snowflake}, 0, {Sheet 2 Go-Live Date}, IFERROR(YEAR(@cell), 0) = Year@row)), 0)

I really want to make the AVG Duration (days) column in my Rollup sheet a column formula column (so I don't have to remember to update when I move sheets from Sheet 1 to Sheet 2.

Any help would be greatly appreciated.

Paul Newcome

Try soemthing more along the lines of...

=(SUMIFS(sheet 1) + SUMIFS(Sheet 2)) / (COUNTIFS(Sheet 1) + COUNTIFS(Sheet 2))

=(SUMIFS({Sheet 1 Duration (days)}, {Sheet 1 Snowflake}, 0, {Sheet 1 Go-Live Date}, IFERROR(YEAR(@cell), 0) = Year@row)) + SUMIFS({Sheet 2 Duration (days)}, {Sheet 2 Snowflake}, 0, {Sheet 2 Go-Live Date}, IFERROR(YEAR(@cell), 0) = Year@row))) / (COUNTIFS({Sheet 1 Snowflake}, 0, {Sheet 1 Go-Live Date}, IFERROR(YEAR(@cell), 0) = Year@row) + COUNTIFS({Sheet 2 Snowflake}, 0, {Sheet 2 Go-Live Date}, IFERROR(YEAR(@cell), 0) = Year@row))

Peggy Parchert

@Paul Newcome - I tried your suggestion and I get a column formula syntax message - I'm really unclear why - I am not using any of the unsupported reference types. Suggestions?

Peggy Parchert

@Paul Newcome - I got it to work - I had to change the column from a column formula to a cell formula; input the formula and then convert it back to a column formula. Final formula looks like this:

=(SUMIFS({Sheet 1 Duration (days)}, {Sheet 1 Snowflake}, 0, {Sheet 1 Go-Live Date}, IFERROR(YEAR(@cell), 0) = Year@row) + (SUMIFS({Sheet 2 Duration (days)}, {Sheet 2 Snowflake}, 0, {Sheet 2 Go-Live Date}, IFERROR(YEAR(@cell), 0) = Year@row))) / (COUNTIFS({Sheet 1 Snowflake}, 0, {Sheet 1 Go-Live Date}, IFERROR(YEAR(@cell), 0) = Year@row) + (COUNTIFS({Sheet 2 Snowflake}, 0, {Sheet 2 Go-Live Date}, IFERROR(YEAR(@cell), 0) = Year@row)))

Thank you for the help! Greatly Appreciated!

Paul Newcome

Glad you were able to get it working. Happy to help. 👍️