SUMIFS multiple criteria including date range

[Rozallyn]

Hi,


Can you please help, I am trying to sumifs using multiple criteria including a date range. However it is returning 0.00 value. The date range seems to be the issue as when I remove this from the formula the other criteria works. Going around in circles and not sure what I am doing wrong, thought it was a straight forward formula. Any ideas would be greatly appreciated.

Basically trying to sum the costs for a job for a specified date range.

The formula:
=SUMIFS('Detail Transaction Listing'!$J:$J,'Detail Transaction Listing'!$D:$D,$B$2,'Detail Transaction Listing'!$A:$A,B15,'Detail Transaction Listing'!$H:$H,">="&Sheet1!$B$5,'Detail Transaction Listing'!$H:$H,"<="&Sheet1!$C$5)


Where:
Detail transaction listing J:J = range to sum
H:H = the range that has the date (the format for this date range is the same as the format on the "date" criteria)
Sheet 1 B5 & C5 is the date criteria field.

Really appreciate your help.

Thanks

 

[etaf]

whats this bit doing
Transaction Listing'!$A:$A,B15,

That should work , if the dates are formatted as dates correctly
assuming
this works OK

=SUMIFS('Detail Transaction Listing'!$J:$J,'Detail Transaction Listing'!$D:$D,$B$2,'Detail Transaction Listing'!$A:$A,B15)

i just used
=SUMIFS($J:$J,$H:$H,">="&$B$5,$H:$H,"<="&$C$5)
on one sheet and it works

does that work with your specific sheets

 

[Rozallyn]

Thank you for your response.

This one has stumped me for sure.

the Transaction Listing'!$A:$A,B15 is referencing another criteria (cost category). when I take out the date criteria this works so I am no suspecting this is the issue.

I've tried the below on my sheet and no luck .

I have checked the formatting of both the date range and the selected date criteria and they seem to be ok.

 

[etaf]

so you have tried just
=SUMIFS($J:$J,$H:$H,">="&$B$5,$H:$H,"<="&$C$5)
and confirmed that between the date you have chosen there are some results
change B5 and C5 to be a range earlier and later than the dates in the range

change the format of the dates in the column H and also B5 and C5 to General and check they all change to a number

 

[Rozallyn]

Hi,

Thank you. Great idea.
I have changed the criteria date to general and this changed to a number; however when I changed the H:H criteria range to general it did not. How do I fix this?
Thanks Regards Roz

 

[etaf]

That means all the dates are TEXT , are they set out like a date ?
whats the format DD/MM/YY or DD-MM-YY etc


in another column on the sheet
try
=Datevalue(h2)
and copy that down the sheet - does that new column now change to a number