Forum Discussion
Solved
xlookup and sum within date range
Hello, My input data is like this. i am trying to get an output like this. summed up values in that particular date range. Please guide me. (The same sample d...
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.