# AverageIf Function

Options
✭✭

I'm sure there is a very easy solution to this one, but need some help...

I want to capture the average of Range 29 IF Range 36 is marked as complete. I also enveloped this in the "Collect" function and the "If Error" function, but still no luck.

=AVERAGEIF({Range 29}, {Range 36}, "Complete")

I am receiving the "#INCORRECT ARGUMENT SET" error.

«1

• ✭✭✭✭✭✭
Options

=AVERAGEIF({Range 36}, "Complete", {Range 29})

See if this does what you are looking for.

• ✭✭✭✭✭✭
edited 06/07/23
Options

Hi @aecross

AVERAGEIF( range, criterion, AverageRange)

range is the range that holds the criterion (Range 36 in your example)

criterion is what you are looking for in the first range ("Complete" in your example, in Range 36)

AverageRange is the range to calculate average (Range 29 in your example)

AverageIf({Range 36}, ="Complete", {Range 29})

Hope this helps!

https://help.smartsheet.com/function/averageif?frame=0&nav=1

• ✭✭
Options

switch {Range 36} and {Range 29}

• ✭✭
Options

Thanks for the help, all!

I've flipped the ranges, but the outcome is "0" when i know it is definitely not. Any ideas how to solve this?

=AVERAGEIF({Range 36} = "Complete", {Range 29})

• ✭✭✭✭✭✭
Options

I'm guessing that Complete is a multi-select drop down column? If so, you're going to need to use CONTAINS to locate Complete in the multiple things selected in a drop down column.

• ✭✭✭✭✭✭
Options

Your formula isn't correct either. You're missing a comma and you don't need the equals sign.

=AVERAGEIF({Range 36}, "Complete", {Range 29})

If this doesn't work, we're back to the multi-select drop down possibility and needing to use CONTAINS to solve it.

• ✭✭✭✭✭✭
Options

How is the Range 36 column formatted?

• ✭✭✭✭✭✭
Options

Probably as a multi-select dropdown column :)

• ✭✭
Options

@Mike TV & @Carson Penticuff - it is a single-select drop down column.

How do i integrate CONTAINS into the formula?

• ✭✭✭✭✭✭
Options

If it's single select then don't use CONTAINS. That won't help fix it.

Include a screenshot of what you're doing so we can help fix.

• ✭✭
Options

@Mike TV - i am looking to get the average of the total only for projects that are listed as COMPLETE in the current project phase (Range 36).

The formula is referencing these items in a different sheet. Current project phase is a single-select dropdown, Total is a text a text column.

• ✭✭✭✭✭✭
Options

The formula I provided earlier seems like it should work in this scenario. Could you try this:

=COUNTIF({Range 36} "Complete")

This should spit out the number of Complete matches. If you verify whether this number is correct, that will tell us whether the issue is with matching this column, or calculating the other column.

• ✭✭✭✭✭✭
Options

• ✭✭
Options

Using =COUNTIF({Range 7}, "Complete") I got 25 which is accurate.

• ✭✭
Options

The last 10 times I referenced the "Current project phase" column it came up as Range 36 ... for my COUNTIF it came up as Range 7. Not sure the issue but it's working now!!!

Thanks for all of the help, I appreciate!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!