xlookup and sum within date range

[KalyanPrasad]

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

[#2]

@KalyanPrasad 

Please see the attached file 

[#3]

@KalyanPrasad

=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.

[#4]

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)

[#5]

@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. 

[#6]

@KalyanPrasad 

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.

[#7]

ok thank you. working well.

[#8 · Accepted solution]

@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...

[#9]

@djclements 

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)

[#10]

@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).

[#11]

@KalyanPrasad 

Please try this one. It will work.

[#12]

thank you. this also looks simple.

[#13]

ok thank you. understood. 🙂

[#14]

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

[#15]

@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...

[#16]

thank you. this seems intelligent.