AVERAGEIFS and Structured Table References

Question

Hi

I am having some difficulty getting structured references correct in the Excel TABLE shown below. For each row, the column AvgAmount should show the average of the Amount field for all rows with the same category and whose Date is equal to or less than than the date in the current row.

In this example, dates are DDMMYYYY and AvgAmount has been determined manually to reflect the answer I expect by formula. And, it cannot be assumed that records will be or will remain in chronological order.

Can anyone help with the formula I should be using in column D please?

Thanks

	A	B	C	D
1	Date	Category	Amount	AvgAmount
2	15/01/2021	A	10	10
3	02/02/2021	A	20	15
4	05/03/2021	B	5	5
5	31/03/2021	A	30	20

Answer 1

In D2:

=AVERAGEIFS([Amount],[Category],[@Category],[Date],"<="&[@Date])

Answer 2

Perfect!

Thank you Hans.