Need help with AVG(Collect - multiple criteria same column

meb_0101

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!

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))))

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

=(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))))

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! :)