Need help with a complex formula

Options
✭✭✭✭✭✭

Attempting to write a formula in a sheet summary field. Is it possible to write a formula that looks for particular rows based on the text value in one column, and then get the AVG of the numbers in a different column on those selected rows?

(ie. If the value in column Task is "Work Order Received", find the average of the Duration column on those rows across all projects on the sheet)

I've tried a bunch of different variations and keep getting different errors. Anyone ever written a successful formula like this?

@Paul Newcome , I will never not tag you in formula questions LOL

Kelly L. Gabel

Owner/Founder

kellylgabel.com

Tags:

• ✭✭✭✭✭✭
edited 05/13/22
Options

Hi Kelly. Are these the results you're looking for?

Example sheet:

Sheet Summary formula:

• ✭✭✭✭✭✭
Options

Also, if you have something like "12 h" in the duration fields then just use a helper column to strip the " h" off and then point the formula to the helper column

• ✭✭✭✭✭✭
Options

YES!!!! I was looking for an Average IF type command but somehow I missed it! You rock Matt! Thank you!

Kelly L. Gabel

Owner/Founder

kellylgabel.com

• ✭✭✭✭✭✭
Options

We do have d in that column but it doesn't seem to be causing any problems with the calculation. Thank you!

Kelly L. Gabel

Owner/Founder

kellylgabel.com

• ✭✭✭✭✭✭
Options

Ok, now one more question...is there a way for me to send the values from the Summary sheet to another sheet? I know how to do cell links and formulas that reference another sheet, but I need to take these values from 6 sheet summaries and then average them all together to then display that final number on a dashboard, but none of the ways I know to pull data allow me to grab the data from the Summary fields...

Kelly L. Gabel

Owner/Founder

kellylgabel.com

• ✭✭✭✭✭✭
Options

Ooh, nevermind...sheet summary report. Duh! I'm all good

Kelly L. Gabel

Owner/Founder

kellylgabel.com

• ✭✭✭✭✭✭
edited 05/13/22
Options

Deleted, didn't read the existing response well enough

• ✭✭✭✭✭✭
Options

AVERAGEIF works if you only have one range/criteria set for evaluation. If (in the future) you need to add to it, you will need to change over to

=AVG(COLLECT(..................))

The "d" should not affect anything if it is in a duration column set up using the dependencies/Gantt. If you did not have those on and they were just text values then you would have to incorporate a way to remove those.

• ✭✭✭✭✭✭
Options

Thanks Paul! I do currently only have one range/criteria set but this is great to know for the future because I have no doubt this customer is going to want more and more magic from me!!

And yes, this is in the duration column auto-created by Gantt so the "d" does not seem to be causing any problems.

Kelly L. Gabel

Owner/Founder

kellylgabel.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!