Averageifs Question

Daryl Lee

Hello!

I am wanting to do an averageifs statement, but it does not seem that is supported. Is there another way that I can calculate an average based off fitting two different criteria?

Right now, I have this equation, which works for when just hitting the one criteria:

=ROUND(AVERAGEIF({System Request}, [Ticket Type 2]1, {Duration}), 2)

However, I also want to break down the average by month, which will add another average criteria.

I know that I can, if needed, create reports that break down the above criteria so I can just use the averageif based of months, but I would prefer to be able to just calculate this through an equation.

Paul Newcome

You will want to use an AVG(COLLECT(...................))

=AVG(COLLECT(range_to_be_averaged, first_criteria_range, first_criteria, second_criteria_range, second_criteria, third_criteria_range, third_criteria, .............................))

Paul Newcome

You will want to use an AVG(COLLECT(...................))

=AVG(COLLECT(range_to_be_averaged, first_criteria_range, first_criteria, second_criteria_range, second_criteria, third_criteria_range, third_criteria, .............................))

Daryl Lee

Thank you so much! That worked perfectly.

Paul Newcome

Happy to help! 👍️

rwilson29376

Paul....Thank you so much...It worked like a charm...

Just out of curiosity...what exactly does Collect do?

Paul Newcome

@rwilson29376 The COLLECT function does exactly that. It collects a range of cells based on the criteria. It is used within another function to tell the main function what to pull from. It allows for A TON of flexibility and adaptability for a wide range of uses since it can be nested inside of almost any other function (other than another COLLECT function).

Megan Harry

Hi Paul,

I am trying to use AVG(Collect) to average a column based on three months listed in another column. I keep getting incorrect argument set. Can you tell me where I am going wrong?

=AVG(COLLECT([Reception Desk Staffing]:[Reception Desk Staffing], Month:Month, "January", [Reception Desk Staffing]:[Reception Desk Staffing], Month:Month, "February", [Reception Desk Staffing]:[Reception Desk Staffing], Month:Month, "March"))

Thank you!

Paul Newcome

@Megan Harry

Try this instead...

=AVG(COLLECT([Reception Desk Staffing]:[Reception Desk Staffing], Month:Month, OR(@cell = "January", @cell = "February", @cell = "March")))

Megan Harry

Thank you so much Paul! This worked perfectly!!

Paul Newcome

@Megan Harry Happy to help. 👍️

Francis Z.

https://community.smartsheet.com/discussion/comment/264174#Comment_264174

Thks Paul! I’ve found essential answers thanks to you!

Francis Z.

https://community.smartsheet.com/discussion/comment/236328#Comment_236328

Hi! One question with the AVERAGEIF(S) fonction: I need to calculate an average when two criteria are met, but these criteria are in two separate columns.

I tried this but failed: =AVERAGEIF(range1; criteria 1; range 2; criteria 2; average range)

ANY IDEA?? Pleeeeeeeeease!

Paul Newcome

FOr multiple range/criteria sets you will need to use the AVG/COLLECT.

=AVG(COLLECT(range to average, criteria range 1, criteria 1, criteria range 2, criteria 2))

Pam Grant

Thank you so much, @Paul Newcome ! You are so helpful. I was looking for "averageifs" and found your AVG(Collect....) solution.

I needed to average turnaround time for the fiscal year. This is my formula, in case it helps someone else:

=AVG(COLLECT({Total Turnaround Time}, {Date of Issue}, >=DATE(2020, 4, 1), {Date of Issue}, <=(DATE(2021, 3, 31))))

I was missing the comma between the criteria range and criteria. Once I recognized that, it worked perfectly.

Randy Van Winkle

Hello, I am thinking this is the solution that I need, but I can't seem to get it right. Trying to average numeric survey results in a column titled "Supply Vendor Performance" and limit response to only ones with drop down selected in "Supply Vendor"

Here is what I have so far:

=AVG(COLLECT(Supply Vendor Performance]:[Supply Vendor Performance], [Supply Vendor]:[Supply Vendor], "ACME"))

Genevieve P.

Hi @Randy Van Winkle

It looks like you're just missing a square parentheses at the beginning of your formula, around the column name [Supply Vendor Performance]... try this:

=AVG(COLLECT([Supply Vendor Performance]:[Supply Vendor Performance], [Supply Vendor]:[Supply Vendor], "ACME"))

Cheers!

Genevieve