# Formula Help - Average IF

Options
✭✭

Hi everyone. I am hoping you can help me. I am trying to average the time it takes individual case managers to complete a case between specific dates.

The reference cells are {CM Text} in the main sheet and it has to equal the name in [Case Manager]@row in the reference sheet

The average comes from the reference identified as {Days to Complete} in the main sheet

The specific date column is referenced as {GS Action Complete} and between the two dates shown in the [Week End]@row and the [Week Begin]@ row.

I keep getting errors on the below formula.

=AVERAGEIFS({Days to Complete}, {CM Text}, [Case Manager]@row, {GS Action Complete}, <=[Week End]@row, {GS Action Complete}, >=[Week Begin]@row)

Any help would be appreciated in getting this fixed.

Tags:

• ✭✭✭✭
Options

You have to create logic within the formula as AVERAGEIFS is not considered a function in Smartsheet.

You could collect the range first then do the average:

=AVG(COLLECT({Days to Complete}, CM Text}, [Case Manager]@row, {GS Action Complete}, <=[Week End]@row, {GS Action Complete}, >=[Week Begin]@row))

or put @cell

=AVG(COLLECT({Days to Complete}, CM Text}, [Case Manager]@row, {GS Action Complete}, @cell<=[Week End]@row, {GS Action Complete}, @cell>=[Week Begin]@row))

• ✭✭✭✭
Options

• ✭✭✭✭
Options

You have to create logic within the formula as AVERAGEIFS is not considered a function in Smartsheet.

You could collect the range first then do the average:

=AVG(COLLECT({Days to Complete}, CM Text}, [Case Manager]@row, {GS Action Complete}, <=[Week End]@row, {GS Action Complete}, >=[Week Begin]@row))

or put @cell

=AVG(COLLECT({Days to Complete}, CM Text}, [Case Manager]@row, {GS Action Complete}, @cell<=[Week End]@row, {GS Action Complete}, @cell>=[Week Begin]@row))

• ✭✭
Options

Leona you are awesome! This worked perfectly thank you so much!

• ✭✭✭✭
Options

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!