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...
=SUMPRODUCT((Data!$B$1:$E$1=$A2)*(Data!$A$2:$A$6>=XLOOKUP(B$1,$E$3:$E$4,$F$3:$F$4))*(Data!$A$2:$A$6<=XLOOKUP(B$1,$E$3:$E$4,$G$3:$G$4))*Data!$B$2:$E$6)
You can use SUMPRODUCT along with XLOOKUP.
OliverScheurich
i have tried out the same formula by myself, with table representations, the formula is showing up as
=SUMPRODUCT((Table1[[#Headers],[opening balance]:[balance]]=[@Report])*(Table1[Date]>=Report!F4)*(Table1[Date]<=Report!G4)*Table1[[opening balance]:[balance]])
// only that i have removed that xlookup for date range to point out those date cells directly.
and the result i m getting right for only the first row. //
am i missing out something ? the same excel which i m trying i have attached.This is the formula for summary 1:
=SUMPRODUCT((Table1[[#Headers],[opening balance]:[balance]]=[@Report])*(Table1[[Date]:[Date]]>=$F$3)*(Table1[[Date]:[Date]]<=$G$3)*Table1[[opening balance]:[balance]])
This is the formula for summary 2:
=SUMPRODUCT((Table1[[#Headers],[opening balance]:[balance]]=[@Report])*(Table1[[Date]:[Date]]>=$F$4)*(Table1[[Date]:[Date]]<=$G$4)*Table1[[opening balance]:[balance]])
However these formulas don't dynamically pick up the data from the summary table. That's why i'd work with XLOOKUP. Just imagine you have summary 1 to summary 100 instead of only summary 1 to summary 2.
- ok thank you. working well.
