AverageIF Formula Help based on Person and Before Certain Date

✭✭

Hi everyone. I am hoping you can all assist me. I have recently taken over a project and have to fix some Smartsheets. I will try my best to explain the problem. I need to average how long it takes from "Assigned" to "Start". The problem is I have two sheets I need to average "Assigned to Start".

The problem I am having is the first sheet only goes until 05/01/23 so I need that average combined with the average from the second sheet which would be all data from 05/01/23 to 12/31/23.

This is the below formula I have but do not know how to average between two sheets =AVERAGEIF({CY23 eAPP Station - Background Investigat Range 12}, <[Begin Date]54) + AVERAGEIF({CY23 eAPP Station (C) - Background Investi Range 5}, <[Begin Date]54)

Any help would be appreciated.

• ✭✭✭✭

Dear @Peppey

You can possibly resolve the issue by combining the Avg(Collect()) functions. Here the Average() function will average only what the Collect() function supplies to it. And You can easily decide what all the Collect() function should supply, and from where / which sheets.

COLLECT Function | Smartsheet Learning Center

Hope this helps.

Have a great day!

• ✭✭✭✭

Dear @Peppey

You can possibly resolve the issue by combining the Avg(Collect()) functions. Here the Average() function will average only what the Collect() function supplies to it. And You can easily decide what all the Collect() function should supply, and from where / which sheets.

COLLECT Function | Smartsheet Learning Center

Hope this helps.

Have a great day!

• ✭✭

Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!