Forum Discussion
xlookup and sum within date range
KalyanPrasad Perhaps something along these lines for Summary 1:
=SUMIFS(XLOOKUP([@Report], Table1[#Headers], Table1), Table1[Date], ">="&$F$3, Table1[Date], "<="&$G$3)Also, if you position the Start and End Dates above the summary table in rows 1 and 2, you can use relative column / fixed row references so the formula can be copied across the summary columns. See attached...
KalyanPrasad Perhaps something along these lines for Summary 1:
=SUMIFS(XLOOKUP([@Report], Table1[#Headers], Table1), Table1[Date], ">="&$F$3, Table1[Date], "<="&$G$3)
Also, if you position the Start and End Dates above the summary table in rows 1 and 2, you can use relative column / fixed row references so the formula can be copied across the summary columns. See attached...
this is perfect and the most simple one.
however i have a small clarification here. in the xlookup's return array what is Table1 ? I assume that it represents the whole Table1. But if i manually selecting the whole table, Table1[#All] is being shown up there and it is not working, can u pls educate me.
=SUMIFS(XLOOKUP([@Report],Table1[#Headers],Table1[#All]),Table1[Date],">="&Report!$F$3,Table1[Date],"<="&Report!$G$3)
KalyanPrasad Table1 is shorthand for Table1[#Data], meaning the data body range only. Table1[#All] refers to the entire table, including Table1[#Headers], Table1[#Data] and Table1[#Totals] (if visible).
- ok thank you. understood. 🙂
