Having problems using SUMIFS with date range and multiple criteria - please help!

[thelittleredfox]

Hi,

I've got a worksheet called 'data sheet' where:

column W contains amounts of money
column A contains "TRUE" and "FALSE" results
column H contains dates

I want to SUM all the payments in column W which also have a value of "TRUE" in column A, AND which fall within a specificed date range.

The formula needs to go on a separate summary sheet within the spreadsheet. The begin and end dates for the date range are entered into cells F5 and I5 on this summary sheet.

I've tried the following formula:

=SUMIFS('data sheet'!W:W,'data sheet'!H:H,">="&F5,'data sheet'!H:H,"<="&I5,'data sheet'!A:A,"TRUE")

but I can't get it to work. When I broke it down, the first part was working, but I couldn't get more than one criteria to work at a time. When I tried to insert either the less than or equal to date or the true or false criteria, I just ended up with a 0 result.

What am I doing wrong? Is there a good way of doing this? Thanks for your help!

 

[thelittleredfox]

If you do look at the uploaded file, was using the formula to calculate David's share, but I seem to have blanked all those values while simplifying. Gareth might work better.

 

[thelittleredfox]

I've narrowed it down and the only bit of the formula not working is the <=date argument. Is there any other way of phrasing this bit? Any ideas why it doesn't work?

 

[thelittleredfox]

=SUMIFS('data sheet'!W:W,'data sheet'!H:H,">="&F5,'data sheet'!H:H,"<="&I5,'data sheet'!A:A,TRUE)

The bit in red is the bit that won't work...

 

[Weazel]

i can't download your sheet at work but did you try adding the INT to the criteria from post 8 to see if that works?

 

[thelittleredfox]

Hi, thanks Weazel,

I have tried it but it doesn't seem to make a difference. The working bit stays working and the bit that doesn't work still doesn't work.

 

[RonB1111]

The problem is your dates in Col B include times, so when I5 is March 10, it is not picking up any March 10. ie: I5 is 42073 but Col B's are 42073.12345.
Add .99999999 to the I5 in your formula like this:

=SUMIFS('data sheet'!D:D,'data sheet'!B:B,">="&F5,'data sheet'!B:B,"<="&I5+0.99999999,'data sheet'!A:A,TRUE)

 

[thelittleredfox]

Thank you, that works! Brilliant

Thanks so much for your help, that has been bugging me all afternoon!

 

[RonB1111]

You're welcome!