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