Average If with three conditions

qgarrett

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!

Kelly Moore

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

Kelly Moore

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

Kelly Moore

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

qgarrett

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.

Kelly Moore

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

qgarrett

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.

Kelly Moore

Glad that did it!

Here's the trick that I almost always use within Collects when I'm using a date function. I promise this won't break your formula 😉- it's just a way to filter out most non-dates that may get added in the future. Note where I placed this criteria within the Collect function.

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

Kelly