=AVGIFS Function Workaround

Todd Tanner

So there isn't an AVGIFS function currently in SmartSheet, but I still need to find a workaround. I came up with:

 

=IF(AND([Project Manager]="Name",[Owner]="Name",[Project Status]="Complete"),AVG([Days to Complete]),"")

 

However I am coming up with a "MISSING OR INVALID PARAMETERS error. I have pulled the averaging portion out and it works fine on its own. 

 

Any ideas?

J. Craig Williams

Todd,

 

You are missing references to the row. The error message can be misleading (at least to me) when this is the case.

I'm not sure that is going to work.

Let us know if it did (or didn't).

I have some ideas but it is time for sleep.

Craig

Dave D

Quickly looking at your formula I see a couple errors.

 

First, [Project Manager], [Owner], are not cell references - they are just the name of a column with brackets. 

 

This is a cell reference:

 

[Project Manager]2

 

or 

 

Owner2

 

If you want reference a range of cells...

 

[Project Manager]2:[Project Manager]10

 

or

 

Owner2:Owner10

 

If you want to reference an entire column:

 

[Project Manager]:[Project Manager]

 

or

 

Owner:Owner

 

You are correct in that there is not an AVGIFS but you can just use COUNTIFS and SUMIFS to get the same results

 

SUMIFS / COUNTIFS = AVGIFS