Averageifs with multiple criteria

Fae Katz

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

Kelly Moore

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

Kelly Moore

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.

Kelly Moore

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

Fae Katz

https://community.smartsheet.com/discussion/comment/278737#Comment_278737

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

Kelly Moore

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.

Fae Katz

(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!!!

Fae Katz

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

Kelly Moore

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)

flevasseur71406

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?

Kelly Moore

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

flevasseur71406

It works great, thank you so much!!!!!!