# AVERAGEIF Function

Hi all -

I am attempting to collect the average of a column of scores IF criteria is met in a separate column. For example:

In the image below, I want to get the average of the Flexibility Score Column IF the Project - Client Name column has a specific name.

I tried the suggestions in previous posts but continue to get #UNPARSABLE or #INVALID

Thank you!

Jen

«1

• ✭✭✭✭✭✭

=AVG(Collect([Flexibility Total]:[Flexibility Total],[Project-Client Name]:[Project-Client Name],"specific name criteria"))

• @Hollie Green - Thank you for sending this along! I tried this with my criteria and it came back #UNPARSEABLE. Here's exactly what I tried:

=AVG(Collect([Flexibility Total]:[Flexibility Total],[Project - Client Name]:[Project - Client Name],"University of Imagination"))

• ✭✭✭✭✭✭
edited 05/24/23

I created a test sheet and it works on my sheet I even copied your formula and pasted once I had the columns created and data put in. What are your column types? Also where are you putting the formula is it on the same sheet, a different sheet, or in the Summary?

@JSabillon

• @Hollie Green - the formula is on a metrics sheet (separate). I wonder what I'm doing incorrectly??

• ✭✭✭✭✭✭

That is what is incorrect if it is on a different sheet you need to create your cross sheet references and you would replace with your reference names.

=AVG(COLLECT({Flexibility Total Reference Name},{Project - Client Reference Name},"University of Imagination"))

• Thanks, Hollie! I actually got it to work with this one:

(actual formula on the metrics sheet):

=AVERAGEIF({Project Team Climate Survey Range 80}, "University of Imagination", {Project Team Climate Survey Range 82})

(general formula on the metrics sheet):

=AVERAGEIF({Project - Client Name Column}, "Client Name", {Flexibility Total Column})

• ✭✭✭✭✭

Hello @Hollie Green

I have similar formula and it works but my difficulty was if I have two conditions, it gives me incorrect argument result. This is my formula, could you help check and advise.

=AVERAGEIF({Test 2023 Duplicate}, 1, {Test 2023 Receive Date}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 3, 31)), {Test 2023 Total Cost}) / 2)

• ✭✭✭✭✭✭
edited 06/13/23

If I am understanding correctly you want the Average of the Test 2023 Total Cost if the Test 2023 Duplicate value is 1 and the Test 2023 Received date is either greater than 1/1/23 or less than 3/31/23. . The below formula should work once your references are created and if I haven't made any typos. Let me know if it works.

=AVG(COLLECT({Test 2023 Total Cost},{Test 2023 Duplicate},1,{Test 2023 Receive Date},AND(@cell>=Date(2023,1,1),@cell<=Date(2023,3,31)))

• ✭✭✭✭✭

Hi @Hollie Green, your understanding is correct. I copied the formula and added to my existing equation, it works in one column. However if I changed the date range or another reference column, it gives me result of #divided by zero.

This one works: =ROUNDUP(AVERAGEIF({Test 2023 Receive Date}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 3, 31)), {Test 2023 Days Delayed}) - AVG(COLLECT({Test 2023 Days Delayed}, {Test 2023 Duplicate}, 1, {Test 2023 Receive Date}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 3, 31)))) / 2)

This gives me error: =ROUNDUP(AVERAGEIF({Test 2023 Receive Date}, AND(@cell >= DATE(2023, 4, 1), @cell <= DATE(2023, 6, 30)), {Test 2023 Days Delayed}) - AVG(COLLECT({Test 2023 Days Delayed}, {Test 2023 Duplicate}, 1, {Test 2023 Receive Date}, AND(@cell >= DATE(2023, 4, 1), @cell <= DATE(2023, 6, 30)))) / 2)

This one too, gives me error: =ROUNDUP(AVERAGEIF({Test 2023 Receive Date}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 3, 31)), {Test 2023 Days 3F+P Completion}) - AVG(COLLECT({Test 2023 Days 3F+P Completion}, {Test 2023 Duplicate}, 1, {Test 2023 Receive Date}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 3, 31)))) / 2)

• ✭✭✭✭✭✭
edited 06/13/23

If you are just trying to get your average that does not include any duplicates I would change the formula to the below if you have your duplicate column set to return a value of 1 or 0. If that isn't what you are trying to do then I would suggest adding an iferror formula to show a result of "" when the error returns for that portion of the formula.

=ROUNDUP(AVG(COLLECT({Test 2023 Total Cost},{Test 2023 Duplicate},0,{Test 2023 Receive Date},AND(@cell>=Date(2023,1,1),@cell<=Date(2023,3,31)))

=ROUNDUP(AVERAGEIF({Test 2023 Receive Date}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 3, 31)), {Test 2023 Days 3F+P Completion}) - IFERROR(AVG(COLLECT({Test 2023 Days 3F+P Completion}, {Test 2023 Duplicate}, 1, {Test 2023 Receive Date}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 3, 31))),"")) / 2)

• ✭✭✭✭✭

@Hollie Green I would like to get the average including only one of the duplicate data that's why I added division function (divided by 2) in the second equation: AVG(COLLECT({Test 2023 Days 3F+P Completion}, {Test 2023 Duplicate}, 1, {Test 2023 Receive Date}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 3, 31)))) / 2)

I copied and tried the IFERROR formula above but it gives me incorrect argument set result.

• ✭✭✭✭✭✭

Try an If statement to go with it that way you can exclude the duplicates if there are not any duplicates.

=IF(INDEX(COLLECT({Test 2023 Days 3F+P Completion}, {Test 2023 Duplicate}, 1, {Test 2023 Receive Date}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 3, 31),1)<1,AVG(COLLECT({Test 2023 Days 3F+P Completion},{Test 2023 Receive Date},AND(@cell>=DATE(2023,1,1),@cell<=DATE(2023,3,31)),AVG(COLLECT({Test 2023 Days 3F+P Completion}, {Test 2023 Duplicate}, 1, {Test 2023 Receive Date}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 3, 31)))) / 2))

• ✭✭✭✭✭

Good morning @Hollie Green. Thank you for checking my concern and providing suggestions, really appreciate it.

I tried also the last formula unfortunately also giving me error result.

• ✭✭✭✭✭✭
edited 06/14/23

What error message is it giving it's possible I have a typo, missing or extra ) or a comma somewhere

Also if you want to share the full set up I can do a test sheet and copy and paste directly from the test sheet

• ✭✭✭✭✭
edited 06/15/23

Good morning @Hollie Green, sorry for my late response.

I get incorrect argument set error using the latest formula.

Here is sniff of the sheet where I reference the columns.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!