Sheet 1:

Sheet 2:

I would like to get the sum of the amount in cells from Sheet1 based on their dates then output onto Sheet2. I have tried searching but to no avail. I have no idea what code to use. I tried this formula =SUM(ARRAYFORMULA(HLOOKUP(H13,Sheet1!J:BR,IF((Sheet1!J:BR=H13),ROW(A:A),100),FALSE))) that I found online but I doesn't do the job.

CodePudding user response：

Note

To not over complicate this we'll fill the table with "Due Date" header to be used in the filter functions later on like so =$A$1.

Sloution 01 - On one go

Paste this in Sheet2 cell B2

=SUM(FILTER(INDEX(QUERY({ FLATTEN(FILTER( Sheet1!$B:$F,Sheet1!$A:$A="Due Date")), FLATTEN(FILTER( Sheet1!$B$1:$F,Sheet1!$A:$A<>"",Sheet1!$A:$A<>"Due Date")) }, " Select * Where Col1 is not null "),,2),INDEX(QUERY({ FLATTEN(FILTER( Sheet1!$B:$F,Sheet1!$A:$A="Due Date")), FLATTEN(FILTER( Sheet1!$B$1:$F,Sheet1!$A:$A<>"",Sheet1!$A:$A<>"Due Date")) }, " Select * Where Col1 is not null "),,1)=B1))

Sloution 02 - Helper table

To genrate a normalized table paste this formula.

= QUERY({ FLATTEN(FILTER( B:F,A:A="Due Date")), FLATTEN(FILTER( B1:F,A:A<>"",A:A<>"Due Date")) }, " Select * Where Col1 is not null ")

And Paste this formula to refrence the normalized tabel

=SUM(FILTER('Normlized table'!$I$2:$I,'Normlized table'!$H$2:$H=B1))