Seeking solution for averageifs()

Options

I would like to calculate the estimated hours it will take me to complete a task based on how long it took me to complete previous tasks of a similar nature. I've included a screenshot of my project sheet with the relevant columns.

Originally I was using the formula: AVERAGEIF([Deck Intensity]:[Deck Intensity], [Deck Intensity]@row, [Act. Hours]:[Act. Hours])

The problem is this formula doesn't take into account the [Status] value when deciding which rows/tasks to include in the average. I do not want to include rows/tasks that are "in progress" or "not started" in my calculation because it will skew my estimated hours lower than what it actually should be. It should only include the average for rows where [Status] is marked as "complete"

I've tried using several variations of =sumif()/countif() but they all end in an error code.

Here's my logic written out in case that helps anyone else:

In cell [Est. Hours]1, average the values in the [Act. Hours] column for every row where [Status] is equal to "Complete" AND [Deck Intensity] is equal to the value in [Deck Intensity]1.

• ✭✭✭✭✭✭
Options

See if this works for you:

=AVG(COLLECT([Act. Hours]:[Act. Hours], Status:Status, ="Complete", [Deck Intensity]:[Deck Intensity], =[Deck Intensity]@row))

Hope this helps!

• ✭✭✭✭✭✭
Options

See if this works for you:

=AVG(COLLECT([Act. Hours]:[Act. Hours], Status:Status, ="Complete", [Deck Intensity]:[Deck Intensity], =[Deck Intensity]@row))

Hope this helps!

• Options

@ker9 That worked! Thank you so much!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!