# Averageifs Question

Options
✭✭✭
edited 01/07/20

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.

Tags:

«13

• ✭✭✭
Options

Thank you so much! That worked perfectly.

• ✭✭✭✭✭✭
Options

Happy to help! 👍️

• Options

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

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

• ✭✭✭✭✭✭
Options

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

• Options

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!

• ✭✭✭✭✭✭
Options

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

• Options

Thank you so much Paul! This worked perfectly!!

• ✭✭✭✭✭✭
Options

@Megan Harry Happy to help. 👍️

• Options

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

• Options

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!

• ✭✭✭✭✭✭
Options

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

• Options

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.

• Options

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

Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!