# Average If with three conditions

✭✭

Hi there,

I have the average if function working correctly with condition but my formula falls apart when I add the other conditions.

This works: =AVERAGEIF({Other Sheet Range 1}, Property@row, {Other Sheet Range 2})

Range 1 is just the property column and range 2 is just the data to average.

This doesn't work: =AVERAGEIF({Market Survey Range 1}, Property@row AND(MONTH({Market Survey Range 3}) = month(Date@row), YEAR({Market Survey Range 3}) = year(Date@row), {Market Survey Range 2})

Basically, if the property name is example, AND if the date falls within the same month and year of the row, give me the average of those rows. I need the average of each month, every month moving forward.

Lookup table

Results table

My results should be five and ten for Nov and Oct but none of the formulas work. Please help!

Tags:

• ✭✭✭✭✭✭

Hey @qgarrett

The AverageIF function only works when there is one criteria to evaluate. When more than one criteria is needed, one needs to use the Average/Collect function.

A Collect function has the syntax COLLECT(range to be collected, range 1, criteria 1, range 2, criteria 2, etc)

=AVG(COLLECT({Other Sheet Score}, {Other Sheet Property}, Property@row, {Other Sheet Date}, MONTH(@cell)=MONTH(Date@row)))

Does this work for you?

Kelly

• ✭✭✭✭✭✭

Hey

Great it's working for 52/54. Try this in your formula to see if it clears the error. Sometimes the Date Functions throw errors when non-dates are in the data set.

=AVG(COLLECT({other sheet score}, {other sheet property}, Property@row, {other sheet date}, IFERROR(MONTH(@cell),0) = MONTH(Date@row), {other sheet date}, IFERROR(YEAR(@cell),0) = YEAR(Date@row)))

Did this clear up the errors?

Kelly

• ✭✭✭✭✭✭

Hey @qgarrett

The AverageIF function only works when there is one criteria to evaluate. When more than one criteria is needed, one needs to use the Average/Collect function.

A Collect function has the syntax COLLECT(range to be collected, range 1, criteria 1, range 2, criteria 2, etc)

=AVG(COLLECT({Other Sheet Score}, {Other Sheet Property}, Property@row, {Other Sheet Date}, MONTH(@cell)=MONTH(Date@row)))

Does this work for you?

Kelly

• ✭✭

Thank you! That worked! Here's the full formula for future humans with the same need.

=AVG(COLLECT({other sheet score}, {other sheet property}, Property@row, {other sheet date}, MONTH(@cell) = MONTH(Date@row), {other sheet date}, YEAR(@cell) = YEAR(Date@row)))

It's working for all 54 properties except I'm getting an #INVALID DATA TYPE error that makes no sense for two of the 54 but manually averaging two is fine. I guess.

• ✭✭✭✭✭✭

Hey

Great it's working for 52/54. Try this in your formula to see if it clears the error. Sometimes the Date Functions throw errors when non-dates are in the data set.

=AVG(COLLECT({other sheet score}, {other sheet property}, Property@row, {other sheet date}, IFERROR(MONTH(@cell),0) = MONTH(Date@row), {other sheet date}, IFERROR(YEAR(@cell),0) = YEAR(Date@row)))

Did this clear up the errors?

Kelly

• ✭✭

Thank you so much Kelly! I was too afraid to touch my perfect formula and risk breaking it so I just deleted the two rows without dates in the source sheet! It worked. Thank you.

• ✭✭✭✭✭✭