Sumifs multiple range

[silambarasan.J]

Hi,

How do sumifs range in number at multiple criteria.

am apply this formula.value return is 0.

=SUMPRODUCT((SUMIFS(Data!$D:$D,Data!$A:$A,ST!$A3:$C3,Data!$B:$B,ST!$A$2,Data!$C:$C,">="&EOMONTH(ST!$E$2,-1)+1,Data!$C:$C,"<="&EOMONTH(ST!$E$2,0))))

please help me.file attached.

[Jonmo1]

Your dates in column C of the Data Page are NOT real dates.
They're just text strings with periods seperating the day.month.year
Those dates need to be converted to a real date that uses / instead of .

I would suggest using
Data - Text To Columns - Deliminated - Next
UNcheck all options - Next
Choose Date - DMY - Finish

[samba_ravi]

E3=SUMPRODUCT((REPLACE(Data!$C$2:$C$3229,1,2,"01")=TEXT(E$2,"DD.MM.YYY"))*(Data!$B$2:$B$3229=$A$2)*(Data!$A$2:$A$3229=$A3:$C3)*(Data!$D$2:$D$3229))

Please Login or Register  to view this content.

Try this and copy across

[samba_ravi]

Yes it is better to change your date format as said by Jonmo1

[silambarasan.J]

wow its super tricks. value return is perfect.

[silambarasan.J]

Sir:nflsales & Jonmo1 ,

THANKS YOU SO MUCH FOR YOUR ADVICE.

I FOLLOWUP.

[Jonmo1]

You're welcome.

[samba_ravi]

Thanks for your feedback and adding reputation