Incomprehensible issue with SUMIFS between 2 dates

Hi,

I can't get to the bottom of this and I feel like I've tried everything.

I keep track of all transactions and use SUMIFS to calculate future credit card payments. The same exact formula has worked for the last 2 years in the same spreadsheet and in fact continues to work in future months, but not in June 2022.

For some reason that is beyond me, when calculating the sum between >=05/16/2022 and <=06/15/2022, the formula appears to be summing ALL amounts <=07/15/2022, so it adds an extra month of data up to and including 07/15/2022.

The formula is:
=SUMIFS(C:C,B:B,">=05/16/2022",B:B,"<=06/15/2022",E:E,"reserve")
Note that the following month's formula:
=SUMIFS(C:C,B:B,">=06/16/2022",B:B,"<=07/15/2022",E:E,"reserve")
Works just fine just as do the previous months' ones!

The SUMIFS in question is in cell C109, with the amounts it's considering (when it shouldn't) are in cells C86 and C88. Next month's formula is in cell C132.
The true total for the date range (>=05/16/2022 and <=06/15/2022) should be 6099.6 (i've added this in cell I109 adding another IF to the formula).

I've checked and all dates are truly formatted as such, and i've tried other versions of the formula (e.g. adding the DATE formula within my SUMIFS, using reference cells, etc.), but nothing seems to work. I can't understand why the formula in I132 works (and all previous ones for over the last 2 years) and the one in I109 doesn't.

Not sure if this is relevant, but the original file lives in a google sheet, but the issue happens in Excel as well. I've attached the file anonymizing the transactions.

Please help before I go insane!

Thank you!

Thanks both!

wk9128, that formula works just like my version =SUMIFS(C:C,B:B,">=05/16/2022",B:B,"<=06/15/2022",E:E,"reserve",F:F,"ok") simply because of that third IF (F:F,"ok"), but it doesn't work if you remove that. You still get the incorrect total.

TMS, I have tried that and it doesn't work, unfortunately!

Hi frca Already attached the attachment with the image in Post#2 and it works fine for me here

Hi Wk9128 it doesn't seem to be working though. In your file, the formula missing ,F:F,"ok" is showing the incorrect amount for me -6,445.37 instead of -6099.6.

Try formula

Formula:

Please Login or Register  to view this content.

Hi Wk, that gives me -345.77, which is the difference between the correct amount (-6099.6) and the incorrect amount (-6445.37). The point though is that the formula should work without that 4th IF, i.e. it should just work as =SUMIFS(C:C,B:B,">="&--"2022/05/16",B:B,"<="&--"2022/06/15",E:E,"reserve"), but it doesn't. I've only added that 4th IF to show the right amount.

Adding an auxiliary column to the cell G2 formula can clearly achieve your purpose =AND(B2>=--"2022/05/16",B2<=--"2022/06/15",E2="reserve",F2 <>"ok") true if TRUE, otherwise not true

Hey wk9128, there are certainly several ways to go about this (including your suggested approach), but column F was only added to simplify calculating the right amount, it's not actually part of my original spreadsheet, and I'm not planning on adding one. I'm really just trying to figure out why cell C109's SUMIFS doesn't seem to be working, while the one in cell C132 is.

I know F86 and F88 say KO and not OK, that's because corresponding cells C86 and C88 should NOT be counted by the formula in cell C109, since row 86 is dated 06/16/2022 row 88 is dated 06/17/2022, which are both "greater than" 06/15/2022. But they are. I only added the values in column F in order to simplify counting what should be included and what shouldn't, and to highlight the 2 cells that are counted incorrectly (C86 and C88). The question is: why is cell C109 including the values in cells C86 and C88 even if the date is "greater than" what's specified in the formula?

Yeah, unfortunately i'm still getting -6445.37 (see screenshot)

I've added your formula to my file in cell I110 and reattached it.

Attachment 784215

I don't know the reason, I haven't changed anything here, and the opening is correct -6099.6, I believe you don't know what caused the setting parameters to run away

Interesting - when I download your file, the SUMIFS in column C don't work at all, while the SUMIFS in column I work perfectly. That's really confusing! Thank you so much for helping, curious to see if anyone else knows why I'm seeing the issues in the original attachment. I'll try and fix my file somehow.

@frca You're Welcome. Glad to help. Thank You for the feedback.
In addition, there is still no reason for the existing problems and how to solve them, only know that the formula direction is correct

Yeah, unfortunately I can't seem to be able to fix my original file, or even a brand new one. Even if I copy and paste the entire sheet content from your attachment into a brand new file, the results are the same as in my original attachment, with the SUMIFS miscalculating the cells

Hey Ali,

Thanks for jumping in!

When I open your file, similar to wk9128, the SUMIFS in column C and the SUMIFS in column I add up to 0, while the SUMIFS in cells J109, and K109 both seem to be correct. When I reset any of the drop-down entries in column E, however, the SUMIFS in cell C109 and K109 both display the wrong amount, while the SUMIFS in cells I109 and J109 both display the correct amount (however this is because of the additional IF I added, the one checking for OK values in column F).

As soon as I open
Attachment 784224
After updating a drop-down entry in column F
Attachment 784225

I wonder if this has something to do with Excel for Mac? However it's super confusing that all other SUMIFS in that column work perfectly fine except for that single one! (And that I'm seeing the same issue in Google Sheets)

Column I works for me as well, but that's only because of the additional IF (F:F,"ok") I added just for the purpose of posting here to make it easier to see what should be included and what shouldn't. My original spreadsheet only has columns A-E.

I'm guessing there's something going on between my version of Excel and yours, because this formula doesn't work for me:

=SUMIFS(C:C,B:B,">="&--"05/16/2022",B:B,"<="&--"06/15/2022",E:E,"reserve")

It keeps including the amounts in cells C86 and C88, even though their date is "greater than" 06/15/2022.

And what's more confusing, is that cell C132 works perfectly, and it's the exact same formula as the one in C109, only the dates are different.

Quick note as I just saw you attached a new workbook. Now when I open it, all SUMIFS in row 109 work perfectly, but the one in cell C132 is adding up to 0 (when it shouldn't). But if I click to edit any formula and just hit enter without changing anything, the values reset themselves.

As soon as I open
Screen Shot 2022-06-17 at 10.37.09 PM.png

After "refreshing" the formula without any changes
Screen Shot 2022-06-17 at 10.38.57 PM.png

When opening the file (0 in column C and I, right values in columns J-K)
Screen Shot 2022-06-17 at 10.49.10 PM.png

After "refreshing" a formula in column C without editing it (C109, I109, K109 showing wrong values, C132, J132 showing correct values)
Screen Shot 2022-06-17 at 10.50.28 PM.png

I'm just glad I'm not alone

Thanks for your help anyway!

It's the inconsistencies that are driving me mad...why oh why is cell C109 not working if cell C132 works just fine???

Did you mean to attach a workbook? And I just mean I'm glad I'm not alone in being out of ideas on the inconsistencies with my original file

I also suspected it has something to do with the date format, however that still wouldn't explain why cell C132 works and C109 does not hence why I'm stumped. It's just so bizarre...

Unfortunately, that formula is the formula I had in my OP and it doesn't work in cell C109 even after changing the date format. It works in cell C132 and in all other months since 2020 (omitted from the file) except for cell C109.

Thank you so much for trying to help!

If anyone else can chime in with some theories, I'd greatly appreciate it. Otherwise, I may just have to start a brand new file or resort to adding another IF to the formula.

Thanks everyone. I figured it out. I knew the correct amount is 6099.6 and not 6445.37 because that's what I get in the statement. I'm ashamed to say that the issue was cell C43 (which is the same formula for the previous period), which incorrectly had "reserve" as account type in cell E43. The formula in I109 was in fact working (as is the one in C132), it was just bad data entry on my part. Can't believe I missed that!! Really appreciate everyone's help here.