AVERAGEIF with an AND

John R.

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

Kelly Moore

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

Kelly Moore

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

John R.

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

laney_white

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}))), "")

@Kelly Moore

laney_white

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}))), "")

heyjay

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))), 
"")