Sum over an array based on date in row and unique identifier in column

[LisaPal]

Hi,

I am looking for a formula to put in cells B2:F7 on Sheet 2.

Notes
- This is a simplified version of the file I am actually working with. The file I am working with has all work-day dates (Mon-Fri) in row 2 on Sheet 1, for 01/01/2021 to 30/06/2022.
- Each Trade Reference is unique, so there are no duplicates.

What I want the formula on Sheet 2 to do, is;
- Look at Sheet 1
- - Sum over B3:R7 for all dates that relate to the relevant month, and the relevant Trade Reference

E.g.
Formula for cell B4 (sheet 2) = find Trade Reference 2 on Sheet 1, and sum up all entries on Sheet 1 that fall between cell B2 and cell C3 on Sheet 2 (i.e. 01/01/21 - 31/01/21).
So here the formula would return all values that appear in cells B4:G4 on Sheet 1 = 1000

Thanks in advance,

Lisa

 

[Fluff]

How about

=SUMPRODUCT((Sheet1!$A$3:$A$10=$A3)*(Sheet1!$B$2:$R$2>=B$2)*(Sheet1!$B$2:$R$2<=EOMONTH(B$2,0))*(Sheet1!$B$3:$R$10))

 

[LisaPal]

How about

=SUMPRODUCT((Sheet1!$A$3:$A$10=$A3)*(Sheet1!$B$2:$R$2>=B$2)*(Sheet1!$B$2:$R$2<=EOMONTH(B$2,0))*(Sheet1!$B$3:$R$10))

That is exactly what I was after - I was playing around with SUMPRODUCT for a while but couldn't get the date to work.

Thanks so much Fluff!

 

[Fluff]

You're welcome & thanks for the feedback.