# Need help with AVG(Collect - multiple criteria same column

✭✭✭
edited 11/02/22

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!

Tags:

• ✭✭✭

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

• ✭✭✭✭✭✭

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

• ✭✭✭

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

• ✭✭✭✭✭✭

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

• ✭✭✭

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

• ✭✭✭✭✭✭

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

Congratulation on finding the right syntaxt !

• ✭✭✭

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!