Need help correct a complex formula

Kelly Gabel

So, I am working with this formula, which works great, but I need to throw in a CONTAINS aspect into it...and what I am trying is not working.

Current formula (that works):

=AVERAGEIF([Project/Task/Issue/Risk Name]:[Project/Task/Issue/Risk Name], ="Stage 2", [Actual Duration]:[Actual Duration])

How would you add in the condition that it also needs to include "NTI" in the [Project]:[Project] column?

@Paul Newcome tagging you as always :)

Paul Newcome

You would need to switch over to an AVG/COLLECT combo to be able to include multiple range/criteria sets.

=AVG(COLLECT([Actual Duration]:[Actual Duration], [Project/Task/Issue/Risk Name]:[Project/Task/Issue/Risk Name], ="Stage 2", Project:Project, CONTAINS("NTI", @cell)))

NOTE: It will follow the syntax of the COLLECT function where the range you want to average comes first (more like a SUMIFS vs a SUMIF).

Paul Newcome

You would need to switch over to an AVG/COLLECT combo to be able to include multiple range/criteria sets.

=AVG(COLLECT([Actual Duration]:[Actual Duration], [Project/Task/Issue/Risk Name]:[Project/Task/Issue/Risk Name], ="Stage 2", Project:Project, CONTAINS("NTI", @cell)))

NOTE: It will follow the syntax of the COLLECT function where the range you want to average comes first (more like a SUMIFS vs a SUMIF).

Kelly Gabel

@Paul Newcome as always, you save me hours of frustration! Thank you!

Paul Newcome

As always, Happy to help. 👍️

Kelly Gabel

@Paul Newcome Ooo one more quick thing, how do I do the reverse? Same formula but does not contain "NTI"?

Paul Newcome

You would wrap the OCNTAINS in a NOT function (don't forget that closing parenthesis haha).

NOT(CONTAINS(.............))

Kelly Gabel

Well that makes sense! Thanks again, Paul!

Paul Newcome

I'm glad it makes sense even with my fat fingers. 🤣

Happy to help. 👍️

Kelly Gabel

LOL I didn't even notice!

Kelly Gabel

@Paul Newcome Ok, I'm a bit confused. So, I have a sheet that I use as a template for the rest of the sheets where I am using these formulas and it is working great on my template sheet, but as soon as I move them over to the sheets that contain live data, I'm getting an #INVALID VALUE error...I've tried troubleshooting a few different things but nothing is impacting this error. Do you know if there any any type of data limit to these formulas being able to calculate? Volume of data seems to be the biggest issue I've been running across with this Program which is why I ask. I don't really see how that should be true though...

I've also tried manually retyping out the whole formula in case there was an issue with the copy/paste between sheets but that didn't do anything for me either.

Paul Newcome

Are you able to provide some screenshots?

Kelly Gabel

Actually, I figured out what was happening and fixed it (all by myself!! LOL) Thanks though!

Paul Newcome

Excellent!