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 data is shared over excel attached)
Thanks & Regards
Kalyan
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...
15 Replies
The solutions all we discussed are based on date range filtering.
What if i want to simply write a month name in header and get the sum of those values in the date table?something like this.
what would the formula be for this one ?
data sheet attached.thank you.
Kalyan
KalyanPrasad Same idea as before, but with EOMONTH to convert the header dates:
=SUMIFS(XLOOKUP([@Report], Table1[#Headers], Table1), Table1[Date], ">"&EOMONTH(B$1, -1), Table1[Date], "<="&EOMONTH(B$1, 0))See attached...
- thank you. this seems intelligent.
- thank you. this also looks simple.
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).
=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.
- Thanks Tejas_shah , but in the report page, if i drag the cell B2 to below column cells B3, B4 & B5, the values are not picking up, instead repeating the same formula. So this is not a complete solution. i need to enter formula for each row individually.
For a complete solution, Should we put a third criteria, that out of the whole data, the column named "so and so" has to be picked up ?
i tried with this formula, but it didnt work for me,
=sumifs(Table1[[opening balance]:[balance]],Table1[#Headers],[@Report],Table1[Date],">="Report!$F$3,Table1[Date],"<="Report!$G$3)
