AverageIF- Date range and multiple criteria from SS source
Hi,
I keep getting unparceable, please help!
Need the average of all days in source 1that meet this criteria: source 2 ="closed", and source 3 has a date range of 1/1/21-3/31/21.
Source 1-{Tracker Range 8}
Source 2- {Tracker Range 4}
Source 3- {Tracker Range 3}
Best Answer
-
Try this:
=AVG(COLLECT({Source 1}, {Source 2}, "Closed", {Source 3}, >=DATE(2021, 1, 1), {Source 3}, <=DATE(2021, 3, 31)))
Answers
-
What type of column is Source 1? The AVERAGEIF function works on numbers. If that column is a date, it won't work on it. What would an average of a date even be? What's your expected result?
-
@Mike TV Yes source 1 is numbers, its count of days containing a separate formula, but this column has some blanks so may need IFBLANK too.
-
Try this:
=AVG(COLLECT({Source 1}, {Source 2}, "Closed", {Source 3}, >=DATE(2021, 1, 1), {Source 3}, <=DATE(2021, 3, 31)))
-
@Mike TV Thanks!! It worked!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 213 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!