Seeking solution for averageifs()

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.

Any ideas? Thanks in advance!

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!