AverageIf Statement Not Right

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!

Find more posts tagged with

Formulas

Comments

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.

Annotation 2019-10-24 134556.png

Annotation 2019-10-24 134602.png

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!

Sign in to join the conversation!

Quick Links

Trending discussions

SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…
Prevent due dates from falling within the same calendar week?
Hi. I am working on a (roughly) annual document review system, in partnership with another team in my organization. They have requested that they receive one document to review per week. But there are more documents to review than there are weeks in the year. So I need to figure out a method for ensuring that I don't send…
How to record date without time
I would like to use automation on a sheet to record the Open and Closed date for a given RAID item. I don't want to record the time, however. Is there a way to exclude a time stamp from the automation? If it is not possible to exclude the time stamp, what formula would I need to use to extract the datae and time into…