AverageIf Statement Not Right

Lindsay AR

Hello,

I am trying to get the average of a column based on the owner. The range is a column with a formula resulting in a number (=[Planned % Complete]@row- [Actual Complete]@row). The criteria is a contact column.  My formula is as follows:

=AVERAGEIF({What The Frog 1}, CONTAINS("Big Bird", {What The Frog 2}))

I have tried several different ways, but continue to get a return of 0. I also tried SumIf, but that did not work either. *Note, I am referencing a different sheet

Thanks for the guidance!

Paul Newcome

Try this...

 

=AVERAGEIF({Contact Column}, CONTAINS("Big Bird", @cell), {Column to Average}

Lindsay AR

It was worth a try, but still returned 0.

Paul Newcome

Are you able to provide screenshots of the source and target sheets? Sensitive/confidential information can be removed, blocked, or replaced with "dummy data".

Lindsay AR

The second screen shot is the source document. The target document is just blank right now, as I haven't fully gotten into it. But I have shown the few cells I have. The first cell in the document is the average without any parameters. 

Paul Newcome

=AVERAGEIF({Contact Column}, FIND("Big Bird", @cell) > 0, {Column to Average}

.

Apparently CONTAINS doesn't like contact type columns...

Lindsay AR

That did the trick. I would not have come up with on my own. Many thanks!

 

Do you know what it would look like to add AND into the function to sort through another parameter? Such as Done is not true. I tried a few different variations of these.... Done Column is currently a check box. 

 =AVERAGEIF(AND{Contact Column}, FIND("Big Bird",@cell)>0, Complete="false", {Column Average}

=AVERAGEIF({Contact Column}, AND( FIND("Big Bird", @cell) > 0, {Done Column}=false), {Column Average})

Paul Newcome

That would be more along the lines of an AVERAGEIFS function. Kind of like COUNTIF vs COUNTIFS or SUMIF vs SUMIFS. Unfortunately AVERAGEIFS doesn't exist within Smartsheet. Feel free to Submit a Product Enhancement Request.

 

In the meantime, you can use the COLLECT function within an AVG function to basically work the same way.

.

Here's the layout:

=AVG(COLLECT({Range to average}, {Criteria Range 1}, Criteria 1, {Criteria Range 2}, Criteria 2, ...............................................................))

.

Here's how it would look using your above info:

=AVG(COLLECT({Column Average}, {Contact Column}, FIND("Big Bird", @cell) > 0, {Done Column}, 0))

.

The basic idea behind it:

You are going to COLLECT all of the data within the first range based off of the criteria set forth within the following range/criteria sets.

When you wrap that in an AVG function, you are saying to average all of that data you just collected.

.

COLLECT is a very flexible and very useful function. It is one that I strongly suggest familiarizing yourself with. It has the potential to take your formulas to an entirely different level.

Lindsay AR

Thank you! I appreciate the explanation. That is very helpful for future uses.

Paul Newcome

Happy to help!