# Avg of a range with 2 criteria

Options
✭✭✭✭

Hi!

I am trying to average a range (which are percentages) based on the Issue Date Year and the Type.

I have tried:

=AVG(COLLECT({Row Averages}, {QA Type}, "Internal", {QA Initial Date}, Year=2020))

and

=AVERAGEIF({QA Type}:{QA Type}, "Internal", {QA Initial Date}:{QA Initial Date}, Year=2020, {Row Averages}:{Row Averages})

and any combo you can think of :) .. still can't get it to return nicely.

Thanks!!

• ✭✭✭✭
Options

As always you are right! Thanks Paul!!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• ✭✭✭✭✭✭
Options

It is how you are referencing the year.

=AVG(COLLECT({Row Averages}, {QA Type}, "Internal", {QA Initial Date}, IFERROR(YEAR(@cell), 0) = 2020))

• ✭✭✭✭
Options

As always you are right! Thanks Paul!!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• Options

On a similar note, my dilema is that I need to find the average for each of the columns below...lets start with JUST column One:

I need to find the Average of this column based on two sets of criteria:

1) I do not want to include zeros in the average

and

2) I want to calculate the averages only for rows dated BEFORE today, which as of today is 1/18/21 (you can see that after these dates, all rows are zero - each day they populate with the number of tasks completed that day...so they are zero now...tomorrow if any tasks area completed a number will show)

So basically, my the lists will grow to show how many tasks were performed by each dept listed in the Columns

Can anyone assist with this dilema?

• ✭✭✭✭✭✭
Options

@Stephanie Kiecker You are going to want to try something along these lines...

=AVG(COLLECT({Date Column}, @cell < TODAY(), {Number Column}, @cell > 0))

• Options

What if I wanted to calculate per week range?

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!