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?

Jan 22 in Others 154 views

## 1 answer to this question.

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): • 63,220 points

## Change date format of cell in excel from dd.mm.yyyy to yyy/mm/dd ( excel version 2013 )

Hello :)   Excel’s Format Cells function can quickly ...READ MORE

## Convert column in excel date format (DDDDD.tttt) to datetime using pandas

Given # s = df['date'] s 0 ...READ MORE

## Sort Excel worksheets based on name, which is a date

Sorting sheets of a workbook are rather ...READ MORE

## Excel plot against a date time x series [closed]

Try using an X-Y Scatter graph with ...READ MORE

## Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

## Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

## IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE