SUMIFS - Greater than first of month, Less than end of month

[pvans267]

Good morning,

I have a sheet (Payments) with the following layout:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Account[/TD]
[TD]Amount[/TD]
[TD]Rec[/TD]
[/TR]
[TR]
[TD]26/10/19[/TD]
[TD]Barclays[/TD]
[TD]100[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]02/11/19[/TD]
[TD]Barclays[/TD]
[TD]100[/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]

On a different tab (October 19), I am trying to use a SUMIFS formula to gather all payments that occurred to Barclays in October 2019.

The formula I currently have is:
=SUMIFS(Payments!$C$2:$C$11,Payments!$B$2:$B$11,$A$4,Payments!$A$2:$A$11,">=" & 1/10/2019,Payments!$A$2:$A$11,"<=" & 31/10/2019)

However, this does not return the correct value. It instead returns the value of 0.

However, if I instead use:
=SUMIFS(Payments!$C$2:$C$11,Payments!$B$2:$B$11,$A$4,Payments!$A$2:$A$11,">=" & 1/10/2019,Payments!$A$2:$A$11,"<=" & TODAY())

I can't work out why the end date is causing the issue. Please can someone suggest how I can get this to work?

Thank you very much

 

[FormR]

Hi, try like this:

=SUMIFS(Payments!$C$2:$C$11,Payments!$B$2:$B$11,$A$4,Payments!$A$2:$A$11,">=1/10/2019",Payments!$A$2:$A$11,"<=31/10/2019")

Or probably better to use:

=SUMIFS(Payments!$C$2:$C$11,Payments!$B$2:$B$11,$A$4,Payments!$A$2:$A$11,">="&DATE(2019,10,1),Payments!$A$2:$A$11,"<=" & DATE(2019,10,31))

At the moment you are comparing your date to 1 divided by 10 divided by 2019 etc

 

[pvans267]

Thank you very much. Your second suggestion works perfectly.

Also, thanks for the explanation, I didn't realise that's what I was effectively doing.