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!

 

[healey21]

Perhaps you could consider using SUMPRODUCT instead of SUMIFS.

Watch this video from MAGIC TRICKS

https://www.youtube.com/watch?v=J525E1lNiOE

 

[Special-K99]

Does this work?

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

Note removal of double quotes

 

[Weazel]

you could try taking the quotes from around the TRUE criteria

 

[thelittleredfox]

Hello,

No it doesn't work, even if I remove the TRUE criteria completely I still can't get the second half of the date bit working?

 

[RonB1111]

Are the dates in Col H in Excel date format? ie: if you change the format to General, does the display value equal something like 42079

 

[thelittleredfox]

Hi, yes, the dates show up as 42073.6842013889 etc.

 

[Weazel]

how about something like...

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

 

[RonB1111]

Could you upload the file to a file sharing service? Make sure anyone with the link can open it without a password.

 

[thelittleredfox]

https://www.dropbox.com/s/co05rjeasm7gry4/Mr excel spreadsheet.xlsx?dl=0

I'm hoping this link works. I've simplified the document so there are no personal details and only the relevant columns remain.