Sumifs behaving oddly in Excel 2013

[PaulLell]

So, I have a spreadsheet that I am using to summarize and trend a bunch of data.

My system looks as such:
Windows 7 Enterprise 64-bit with SP 1
Intel i7 quad core 2.2GHz
8GB RAM
MS Excel 2013 Professional Plus 32-bit edition

In this spreadsheet I have multiple worksheets to separate my sources and various breakouts/views.
Two of these sheets are being populated by embedded SQL queries which are manually fired off within the spreadsheet.
These two worksheets are large once populated (Close Data is about 32k rows and Open Data is about 880k rows).

On one of my summary sheets I am using a sumifs function to total dollars for records that fall between two dynamic dates:

=SUMIFS('Close Data'!C:C,'Close Data'!U:U,"<="&Breakout!B17,'Close Data'!U:U,">="&Breakout!B18)

Where Close Data column C is dollar amounts formatted as general and U is a date formatted as date. Breakout B17 is date generated dynamically by:

=EOMONTH(TODAY(),-1)

B18 is today -2,B19 is -3 and so on.

All of this is working perfectly.

Now, on another worksheet I have the following formula:

=SUMIFS('Close Data'!F:F,'Close Data'!U:U,"<="&Trends!I4,'Close Data'!U:U,">"&Trends!H4)

Where Close Data column F is another set of dollar amounts formatted as general and Trends I4 (and H4, etc.) is a dynamically generated date just as above (set horizontally this time rather than vertically). However, in this case, the formula returns 0 no matter what else I do or how I attempt to adjust it.

I am having the same problem when pulling data from the Open Data worksheet as well.

I can copy and paste the formula that woks to a new worksheet and it will work still, but as soon as I change the date criteria to the date fields on the new worksheet, even though they are being built/populated the same way, the formula starts returning 0 and will never add up properly again.

Although, interestingly enough, if I change it to the following:

=SUMIFS('Close Data'!$F:$F,'Close Data'!$U:$U,"="&Trends!I4)

It does grab values. So, it appears to be having trouble resolving the date range for some reason that I can't identify.

Frankly, I'm at a loss here. I've been bashing at this for days and am at wit's end. Any thoughts on what I've done wrong here? I use this formula all the time for even more complex applications/analyses. The only thing that is different here is the embedded SQL, but why would that affect one worksheet and not another?

Thoughts?

 

[cyrilbrd]

Hi, just being curious, is Trends!I4 superior to Trends!H4? if H4 is > to I4 your formula will return zero (0).

 

[PaulLell]

Hi, just being curious, is Trends!I4 superior to Trends!H4? if H4 is > to I4 your formula will return zero (0).

Heya! Thank you for your reply.

To answer your question, no. The dates are in descending order.

That said, I got this to work finally. The issue seems to have been to do with the formatting of the date field in the source data. I adjusted my SQL to pull the date in a different format and it all started working. No idea why it was working on one sheet and not the other though. :/

 

[cyrilbrd]

Cool, glad you got it sorted.