Need help with a complex formula

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

Find more posts tagged with

Formulas

Accepted answers

All comments

Mike TV

@Kelly Gabel

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

Example sheet:

Sheet Summary formula:

=AVERAGEIF(Task:Task, ="Work Order Received", Duration:Duration)

Mike TV

@Kelly Gabel

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

Kelly Gabel

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

Kelly Gabel

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

Kelly Gabel

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 Gabel

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

L_123

Deleted, didn't read the existing response well enough

Paul Newcome

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.

Kelly Gabel

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.