SOLVED

# xlookup and sum within date range

Copper Contributor

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

(The same sample data is shared over excel attached)

Thanks & Regards

Kalyan

15 Replies

# Re: xlookup and sum within date range

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

# Re: xlookup and sum within date range

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,

# Re: xlookup and sum within date range

@OliverScheurich

i have tried out the same formula by myself, with table representations, the formula is showing up as

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

# Re: xlookup and sum within date range

This is the formula for summary 1:

This is the formula for summary 2:

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.

# Re: xlookup and sum within date range

ok thank you. working well.
best response confirmed by KalyanPrasad (Copper Contributor)
Solution

# Re: 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...

# Re: xlookup and sum within date range

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.

# Re: xlookup and sum within date range

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

# Re: xlookup and sum within date range

Please try this one. It will work.

# Re: xlookup and sum within date range

thank you. this also looks simple.

# Re: xlookup and sum within date range

ok thank you. understood. 🙂

# Re: xlookup and sum within date range

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

# Re: xlookup and sum within date range

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

# Re: xlookup and sum within date range

thank you. this seems intelligent.
1 best response

Accepted Solutions
best response confirmed by KalyanPrasad (Copper Contributor)
Solution

# Re: 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...