Forum Discussion
Calculating amounts between two month dates using AVERAGEIFS?
How do I use the AVERAGEIFS function to filter out any amounts between a start month and end month (e.g between October - month 10, and March - month 3)? Any amounts that have monthly dates that fall within that range should be calculated - the day number/year is not necessary, just if it's between and including month 10 and less than and including month 3.
I know I can use the AVERAGE + FILTER function but trying to do it all in one step.
Thanks in advance!
2 Replies
Hi J-Des000
Because of the OR situation your logic creates, greater than or equal to month 10 OR less than or equal to month 3, we have to capture the criteria in different formulas. And for AVERAGE, we would have to compute it from its native SUM and COUNT breakdown.
Below is an example, to stick as closely as possible to your original AVERAGEIFS formula ask, I have created a needed month column and used SUMIFS and COUNTIFS. Sadly, AVERAGEIFS couldn't handle the situation given.
I've attached the Excel file with the formulas intact for your ease of use and review.
I hope this answers your question. If yes, kindly mark this as answer.If your date range spans from October (month 10) to March (month 3) and crosses the year boundary, you'll need to handle the year change carefully. For example, if you have data spanning from October 2023 to March 2024, you need to adjust the year handling within your formula.
Example:
Column A with dates (A2:A100)
Column B with amounts (B2:B100)
For cases where the month range crosses into a new year:
=AVERAGEIFS(B2:B100, A2:A100, ">="&DATE(YEAR(TODAY())-1, 10, 1), A2:A100, "<="&DATE(YEAR(TODAY()), 3, 31))
