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!
Best Answers
-
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
Answers
-
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.
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!