Averageifs with multiple criteria
Hey! I'm trying to return the average of a task that is completed from multiple houses
I need to check whether it is the task I am looking for and if it is complete, and then return the average duration for the tasks that follow those criteria
If averageif had multiple criteria I could do this easily, but it does not
ND is the duration average converted to numbers, and the other two are self explanatory
helppp
Best Answers

Hey @Fae Katz
=AVG(COLLECT()) will do the trick for you. The COLLECT function follows the syntax of SUMIFS
COLLECT(range to be averaged, range1, criteria1, range2, criteria2, range3, criteria3, etc)
If I understood your criteria correctly, it would look like this
=AVG(COLLECT(ND:ND, [Task Name]:[Task Name], [Task Name]@row, [% Complete]:[% Complete], 1))
Kelly

Hey @Fae Katz
Sorry you're getting an error. The formula is residing in a Text/Number column?
Let's add one more condition to the Collect where we eliminate any blank cells in the ND column.
Please insert
ND:ND, <>""
If you still get an error we can remove the criteria one by one from the formula to see which column is the culprit.
Answers

Hey @Fae Katz
=AVG(COLLECT()) will do the trick for you. The COLLECT function follows the syntax of SUMIFS
COLLECT(range to be averaged, range1, criteria1, range2, criteria2, range3, criteria3, etc)
If I understood your criteria correctly, it would look like this
=AVG(COLLECT(ND:ND, [Task Name]:[Task Name], [Task Name]@row, [% Complete]:[% Complete], 1))
Kelly

Hey! thanks for the reply
I pasted the formula as follows:
=AVG(COLLECT(ND:ND, [Task Name]:[Task Name], "Frame / MEP", [% Complete]:[% Complete], 1))
and it returns #INVALID VALUE :(
I made sure to copy a value on the Task Name and paste on the condition, but it does not work

Hey @Fae Katz
Sorry you're getting an error. The formula is residing in a Text/Number column?
Let's add one more condition to the Collect where we eliminate any blank cells in the ND column.
Please insert
ND:ND, <>""
If you still get an error we can remove the criteria one by one from the formula to see which column is the culprit.

(There is an answer on the end of this post, but something weird still happens with the specific sheet I was working on. I'm just leaving the steps I followed to troubleshoot it)
Yes it is set as a text/number
=AVG(COLLECT(ND:ND, [Task Name]:[Task Name], "Frame / MEP", [% Complete]:[% Complete], 1, ND:ND, <>""))
=AVG(COLLECT(ND:ND, [% Complete]:[% Complete], 1, ND:ND, <>""))
=AVG(COLLECT(ND:ND, [% Complete]:[% Complete], 1))
=AVG(COLLECT(ND:ND, [Task Name]:[Task Name], "Frame / MEP", ND:ND, <>""))
=AVG(COLLECT(ND:ND, [Task Name]:[Task Name], "Frame / MEP"))
all of these return the same error
but If I try to get the AVG of the number in "Net Terms", it works! (the reason why i had to change the task is due to Frame / MEP having no net terms
=AVG(COLLECT([Net Terms]:[Net Terms], [Task Name]:[Task Name], "Pad Stake Survey", [% Complete]:[% Complete], 1))
So I am thinking the problem is using the ND in formulas, but here comes the weird part
I did this in another sheet (it's very similar, just changes which properties are being dumped in that sheet, one comes from only one location, the other one comes from all of them) and it worked!
But I used the same formula and same column types in both sheets???
It makes me very confused haha, but sure
Now I'm going to add a "Location" column to differentiate between the two locations I want to average and have two summary fields, one for each, instead of them being on separate, filtered sheets.
Thank you so much for your help!!!

Okay I'm back haha
So, I tried to do based on the location written out or making the location into a number, either returned #NO MATCH
I'm pretty sure I wrote the formula correctly, I don't know why it's not working
=AVG(COLLECT(ND:ND, [Task Name]:[Task Name], "Building Permit", [% Complete]:[% Complete], 1, ND:ND, <>"", LocID:LocID, 1))

Hey Fae
From the limited screenshot, I don't see any Completions that =100. Also, I see your ND is a formula.
Could you add an IFERROR to that ND formula? I don't know if the formula isn't working because there are errors in the column, or if the AVG formula isn't recognizing the number as a VALUE
=IFERROR(whatever your current formula is exactly as written including all parentheses,0)

This formula has been a great help for what I'm working on. But I'm getting #divide by zero, when my quantity is a zero. Not sure if IFERROR would work?
=AVG(COLLECT({7.All Construction  Order not Shipped Range 1}, {7.All Construction  Order not Shipped Range 5}, ="BB", {7.All Construction  Order not Shipped Range 4}, ="DD"))
Any ideas?

Hey @flevasseur71406
Yes, the IFERROR will work for you. Note that the IFERROR wraps around your entire original formula, parentheses and all
IFERROR(AVG(COLLECT({7.All Construction  Order not Shipped Range 1}, {7.All Construction  Order not Shipped Range 5}, ="BB", {7.All Construction  Order not Shipped Range 4}, ="DD")), 0)
In this case, I have it showing zero if an error occur. You can change that to display text (replace the zero with quotes and your text string) if you prefer
As you continue to grow your formula building, you can replace the generically generated smartsheet range numbers, eg, {7.All Construction  Order not Shipped Range 1} changing the name to the column you are actually using BEFORE you click the final Insert references. This will help you better identify your ranges and formulas in the future.
Does the above formula work for you?
Kelly

It works great, thank you so much!!!!!!
Help Article Resources
Categories
Check out the Formula Handbook template!