Need help with AVG(Collect - multiple criteria same column

I'm trying to use Avg(Collect to get the average number of days from another sheet. My problem is I need to search one column for 2 key words.

Is Avg(Collect similar to Countifs( where you can't do this? The below formulas work (XXX is a holder) separately but I'm at a loss for how to combine them to yield the correct answer. I tried combining them but the average was incorrect.

=AVG(COLLECT({Active#DaysOpen}, {ActiveSegment}, HAS(@cell, "XXX"), {ActiveRequestStatus}, CONTAINS("ready", @cell)))

=AVG(COLLECT({Active#DaysOpen}, {ActiveSegment}, HAS(@cell, "XXX"), {ActiveRequestStatus}, CONTAINS("assign", @cell)))

Thank you!

Find more posts tagged with

Formulas

Accepted answers

meb_0101

So this ended up working but sheesh... wish there was a simpler way lol

=(SUM(COLLECT({Active#DaysOpen}, {ActiveSegment}, HAS(@cell, "XXX"), {ActiveRequestStatus}, CONTAINS("assign", @cell))) + SUM(COLLECT({Active#DaysOpen}, {ActiveSegment}, HAS(@cell, "XXX"), {ActiveRequestStatus}, CONTAINS("ready", @cell)))) / (COUNT(COLLECT({Active#DaysOpen}, {ActiveSegment}, HAS(@cell, "XXX"), {ActiveRequestStatus}, CONTAINS("assign", @cell))) + COUNT(COLLECT({Active#DaysOpen}, {ActiveSegment}, HAS(@cell, "XXX"), {ActiveRequestStatus}, CONTAINS("ready", @cell))))

All comments

Christian G.

You might have to calculate the average yourself:

=(Count(COLLECT({Active#DaysOpen}, {ActiveSegment}, HAS(@cell, "XXX"), {ActiveRequestStatus}, CONTAINS("assign", @cell))+ Count(COLLECT({Active#DaysOpen}, {ActiveSegment}, HAS(@cell, "XXX"), {ActiveRequestStatus}, CONTAINS("ready", @cell))))/ Count({Active#DaysOpen})

meb_0101

@Christian G. Thank you for the suggestion. I tried that but it did not work either.

Christian G.

Which part does not work ? Can you count with a formula both tje status independently?

meb_0101

So this ended up working but sheesh... wish there was a simpler way lol

Christian G.

@meb_0101 It is complex work. But you only do it once!

Congratulation on finding the right syntaxt !

meb_0101

@Christian G. aha had to use the formula for 8 other cells but glad to have the base formula done! Thank you for helping me figure it out! :)

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.