=AVGIFS Function Workaround

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?

Find more posts tagged with

Formulas

Real Estate

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

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

Owner2

If you want reference a range of cells...

[Project Manager]2:[Project Manager]10

Owner2:Owner10

If you want to reference an entire column:

[Project Manager]:[Project Manager]

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