AverageIf Function

aecross

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.

Carson Penticuff

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

See if this does what you are looking for.

ker9

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

Yamaha

switch {Range 36} and {Range 29}

aecross

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

Mike TV

@aecross

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.

Mike TV

@aecross

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.

Carson Penticuff

How is the Range 36 column formatted?

Mike TV

@Carson Penticuff

Probably as a multi-select dropdown column :)

aecross

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

How do i integrate CONTAINS into the formula?

Mike TV

@aecross

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.

aecross

@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.

Carson Penticuff

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.

Mike TV

@Carson Penticuff

Don't forget your comma

aecross

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

aecross

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!