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

[pvans267]

Good morning,

I have a sheet (Payments) with the following layout:

Date	Account	Amount	Rec
26/10/19	Barclays	100	Yes
02/11/19	Barclays	100	Yes

<tbody>
</tbody>

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.