AVERAGEIF with an AND
I'm trying to find the average lead time for completed projects that were started this year and I'm having an awful time. Full disclosure, I'm not great a writing formulas.
I can get the average lead time for completed projects. This works just fine:
=AVERAGEIF({Status}, CONTAINS("complete", @cell), {Lead Time})
And I can get the average lead time for projects started this year. This works, too (I used IFERROR because some projects on the list haven't been started yet, so have no start date):
=AVERAGEIF({Start Date}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), {Lead Time})
But I can't seem to get both parameters to play nicely together. This returns "#INVALID DATA TYPE":
=AVERAGEIF(AND({Start Date}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), {Status}, CONTAINS("complete", @cell)), {Lead Time})
I'm sure that I'm just missing something simple, but I can't figure out what it is. Any help would be greatly appreciated.
Thanks,
John Rudolph
Best Answer
-
Hello @John R.
The AVERAGEIF function only works with one criteria. When needing multiple criteria to filter the results, one must use an AVG/COLLECT.
=AVG(COLLECT({Lead Time},{Start Date}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()),{Status}, CONTAINS("complete", @cell)))
Will this work for you?
Kelly
Answers
-
Hello @John R.
The AVERAGEIF function only works with one criteria. When needing multiple criteria to filter the results, one must use an AVG/COLLECT.
=AVG(COLLECT({Lead Time},{Start Date}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()),{Status}, CONTAINS("complete", @cell)))
Will this work for you?
Kelly
-
Hello Kelly,
That makes so much sense it's almost ridiculous. And it works perfectly. Thank you so much for your help. Smartsheet Community comes through again!
John
-
Hello!
I am trying to do something like this. Looking to get SLA times for specific types of orders within specific timeframes (i.e., what was our SLA for XYZ orders in July). I tried to mimic the above formula with no luck. (I tried with and without IFERROR - this is new tracking so we have a lot of SLA fields that are blank)
=IFERROR(AVG(COLLECT({MTM - Completed Orders Archive Range 1}, {submission date}, @cell >= DATE(2024, 7, 1), {submission date}, @cell <= DATE(2024, 7, 31), {order#}, CONTAINS("p2", @cell}))), "")
-
Hello!
I'm looking for something similar. I'm trying to get SLAs for specific orders in specific time ranges - i.e., what was the SLA for the XYZ orders in July). I tried to mimic the above formula with the exception that I added IFERROR to the beginning because this is new and some of the data is blank.
=IFERROR(AVG(COLLECT({MTM - Completed Orders Archive Range 1}, {submission date}, @cell >= DATE(2024, 7, 1), {submission date}, @cell <= DATE(2024, 7, 31), {order#}, CONTAINS("p2", @cell}))), "")
-
Looks like you have an extra } after the CONTAINS function.
=IFERROR(AVG(COLLECT( {MTM - Completed Orders Archive Range 1}, {submission date}, @cell >= DATE(2024, 7, 1), {submission date}, @cell <= DATE(2024, 7, 31), {order#}, CONTAINS("p2", @cell))), "")
...
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!