Sumproduct value with max date across two sheets

[silambarasan.J]

Hello forum Experts,

I need sumproduct value corresponding to the range against with max date.

In Data1 file attached two sheets Data1 and Data2 to ref which is the max date to return value corresponding to the range.

To apply the following formulas in
E4=SUMPRODUCT(--([DATA.XLS]DATA1!$A:$A=A4)*--([DATA.XLS]DATA1!$E:$E=MAX(MAX([DATA.XLS]DATA1!$C:$C),MAX([DATA.XLS]DATA2!$C:$C))))*([DATA.XLS]DATA1!$D:$D)/100000+SUMPRODUCT(--([DATA.XLS]DATA2!$A:$A=A4)*--([DATA.XLS]DATA2!$C:$C=MAX(MAX([DATA.XLS]DATA2!$C:$C),MAX([DATA.XLS]DATA1!$C:$C))))*([DATA.XLS]DATA2!$D:$D)/100000 value return wrongly.

please give your suggestions.file attached.

[silambarasan.J]

please help me someone

[silambarasan.J]

The following formula apply column E4 from Required format sheet value return error "#valuue"

E4=SUMPRODUCT(--([DATA.XLS]DATA1!$A:$A=A4)*--([DATA.XLS]DATA1!$E:$E=(MAX(MAX([DATA.XLS]DATA1!$C:$C),MAX([DATA.XLS]DATA2!$C:$C))))*[DATA.XLS]DATA1!$D:$D)/100000+SUMPRODUCT(--([DATA.XLS]DATA2!$A:$A=A4)*--([DATA.XLS]DATA2!$C:$C=(MAX(MAX([DATA.XLS]DATA1!$C:$C),MAX([DATA.XLS]DATA2!$C:$C))))*[DATA.XLS]DATA2!$D:$D)/100000

Please help me some one.