averageifs w/ multiple average ranges in different columns

0
votes

=IFERROR(AVERAGEIFS('Journal Input'!$AO$4:$AO$480,'Journal Input'!$O$4:$O$480,">="&F10,'Journal Input'!$O$4:$O$480,"<="&EOMONTH(F10,0)),"N/A")

This formula works but I need multiple average ranges added to this equation. My first average data range is AO but I have others in different columns (BB, BM, etc.) I would like to average along w/ AO. My multiple criteria's cover the beginning and end of the month F10. Perhaps an array is necessary? Any help is appreciated. Thanks Mike Link to picture example here

1
arraysexceldaterangemultiple-columns
what have you tried to add the multiple average ranges? And can you give us an example of what your data might look like? Are you trying to add together the averages of the four columns? What would your end product ideally look like? - girlvsdata
Hi. Thanks for the help. I have tried to add other ranges to the equation but get an error message. I edited the post and posted a picture to help solve this problem. I need the monthly averages calculated based upon the start and end dates of the month. Thanks - mjac

1 Answers

1
votes

Here's a couple options. Enter this as an array formula (Ctrl+Shift+Enter):

=IFERROR(AVERAGE(IF(('Journal Input'!$O$4:$O$480>=F10)*('Journal Input'!$O$4:$O$480<=EOMONTH(F10,0)),CHOOSE({1,2,3},'Journal Input'!$AO$4:$AO$480,'Journal Input'!$BB$4:$BB$480,'Journal Input'!$BM$4:$BM$480))),"N/A")

Or, since it's the same conditions for all columns, you can average the AVERAGEIFS of each column (this doesn't need to be entered as an array formula):

=IFERROR(AVERAGE(AVERAGEIFS('Journal Input'!$AO$4:$AO$480,'Journal Input'!$O$4:$O$480,">="&F10,'Journal Input'!$O$4:$O$480,"<="&EOMONTH(F10,0)),AVERAGEIFS('Journal Input'!$BB$4:$BB$480,'Journal Input'!$O$4:$O$480,">="&F10,'Journal Input'!$O$4:$O$480,"<="&EOMONTH(F10,0)),AVERAGEIFS('Journal Input'!$BM$4:$BM$480,'Journal Input'!$O$4:$O$480,">="&F10,'Journal Input'!$O$4:$O$480,"<="&EOMONTH(F10,0))),"N/A")