Excel AVERAGEIFS Date discrepancy

Question

Based on two factors, I would like the average of Column B. that it occurred the previous year and text requirements from another column. by year, the average For testing purposes, I've included a Year column in the example, but I don't want to add it to every datasheet.

=AVERAGEIFS(Table1[Unit], Table1[Date], "="&YEAR(TODAY())-1, Table1[Text], "Up") 

Throws a DIV/0 error.

I believe I need to define the Date range by year.. like (YEAR(Table1[Date]) but it doesn't work.

=AVERAGEIFS(Table1[Unit], (YEAR(Table1[Date]), "="&YEAR(TODAY())-1, Table1[Text], "Up")

I can get an IF statement to work on a single cell but is there is way to get this to work in a column?

Answer 1

When defining a range, you are unable to use formulas; therefore, you must either use a helper column or something similar:

=AVERAGEIFS(Table1[Unit],Table1[Date],">="&(DATE(YEAR(TODAY())-1,1,1)),Table1[Date],"<="&(DATE(YEAR(TODAY())-1,12,31)),Table1[Text],"Up")

It checks if date is less than 2022/12/31 (DATE(YEAR(TODAY())-1,12,31))and more than 2022/01/01 (DATE(YEAR(TODAY())-1,1,1))

Result ((3+7)/2=5):