SUMIFS formula modification help

[JumboCactuar]

Hi,

I have this formula which combines 2 SUMIFS

=SUMIFS(I:I,B:B,B9,A:A,"<="&TODAY())+SUMIFS('Sheet2'!H:H,'Sheet2'!A:A,B9,'Sheet2'!O:O,"<="&TODAY())

Instead of TODAY()
I want it to use the most recent date in both parts of the formula
example:
In the 1st SUMIF - latest date = 11/01/2024
2nd SUMIF - latest date = 06/12/2023

So both SUMIFS should use 11/01/2024

=SUMIFS(I:I,B:B,B9,A:A,"<=11/01/2024)+SUMIFS('Sheet2'!H:H,'Sheet2'!A:A,B9,'Sheet2'!O:O,"<=11/01/2024)

Hope this makes sense, and not sure if i need some helper columns
Thanks for any help

 

[SanjayGMusafir]

Check this and revert -

=Let(mDt,Max(A:A),SUMIFS(I:I,B:B,B9,A:A,"<="&mDt)+SUMIFS('Sheet2'!H:H,'Sheet2'!A:A,B9,'Sheet2'!O:O,"<="&mDt))

Note: It shall use the latest (largest) date in column A:A

 

[JumboCactuar]

Check this and revert -

=Let(mDt,Max(A:A),SUMIFS(I:I,B:B,B9,A:A,"<="&mDt)+SUMIFS('Sheet2'!H:H,'Sheet2'!A:A,B9,'Sheet2'!O:O,"<="&mDt))

Note: It shall use the latest (largest) date in column A:A

Hi
Thanks but i think that is wrong

I have dates in the current sheet (Column A)
then dates in Sheet 2 (Column O)

I need it to use the largest date of the two

 

[SanjayGMusafir]

I need it to use the largest date of the two

Check this and revert -

=Let(mDt,Max(Max(A:A),Max(Sheet2'!O:O)),SUMIFS(I:I,B:B,B9,A:A,"<="&mDt)+SUMIFS('Sheet2'!H:H,'Sheet2'!A:A,B9,'Sheet2'!O:O,"<="&mDt))

The above shall search the largest date on the two sheets.

 

[JumboCactuar]

Check this and revert -

=Let(mDt,Max(Max(A:A),Max(Sheet2'!O:O)),SUMIFS(I:I,B:B,B9,A:A,"<="&mDt)+SUMIFS('Sheet2'!H:H,'Sheet2'!A:A,B9,'Sheet2'!O:O,"<="&mDt))

The above shall search the largest date on the two sheets.

Thanks Again but that doesn't select the largest date based on the other criteria of the SUMIFS

 

[SanjayGMusafir]

Thanks Again but that doesn't select the largest date based on the other criteria of the SUMIFS

Share XL2BB of actual data to understand the complexity of situation.