SUMIFS Forumla with Date Range Returning Unexpected Values

[HawaiianShirts]

I'm working on my personal finances, which I've done for years with Excel. The last version was in the XP edition, and I kept that for a long time. To calculate my monthly expense totals, I manually entered a formula each time. Now that I'm using Office 2010, I figured I could make my life easier with some new formulae. Last night, I fought with a SUMIFS formula for a long time, and I can't seem to get it to work.

I have two worksheets--Accounts and Budgeting. In the Accounts sheet, I have a date column, a column that categorizes each expense I enter (Groceries, Transportation, Entertainment, Utilities, etc.), and a column for the amount. Simplified Example:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Date (Column A)[/TD]
[TD]Other Details (Columns B-D)[/TD]
[TD]Category (Column E)[/TD]
[TD]Amount (Column F)[/TD]
[/TR]
[TR]
[TD]04-May-2014[/TD]
[TD]Details[/TD]
[TD]Groceries[/TD]
[TD]104.65[/TD]
[/TR]
[TR]
[TD]10-Jun-2014[/TD]
[TD]Details[/TD]
[TD]Utilities[/TD]
[TD]74.95[/TD]
[/TR]
</tbody>[/TABLE]

In the Budgeting sheet, Column A has dates and columns B through whatever will be for each expense category. Like so:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Date (Column A)[/TD]
[TD]Groceries (Column B)[/TD]
[TD]Utilities (Column C)[/TD]
[TD]Other Categories (D, E, F, etc.)[/TD]
[/TR]
[TR]
[TD]01-May-2014[/TD]
[TD]=sumifs[/TD]
[TD]=sumifs[/TD]
[TD]=sumifs[/TD]
[/TR]
[TR]
[TD]01-Jun-2014[/TD]
[TD]=sumifs[/TD]
[TD]=sumifs[/TD]
[TD]=sumifs[/TD]
[/TR]
</tbody>[/TABLE]

This is the formula I finally came up with that I thought should work to calculate the total expense for a given month, based on the dates in Column A in Budgeting. The 44444 end cell reference is just an arbitrary max I made up.

=SUMIFS(Accounts!$F$4:$F$44444,$A$4:$A$44444,">="&Budgeting!A3,Accounts!$A$4:$A$44444,"<"&Budgeting!A4,Accounts!$E$4:$E$44444,"="&Budgeting!$B$1)

When I repeat that down each row, the cell references change the way I hoped they would, but the totals that come back don't make sense to me. Here's what I got for the Groceries category.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Category:[/TD]
[TD]Groceries[/TD]
[/TR]
[TR]
[TD]Dates[/TD]
[TD](Blank)[/TD]
[/TR]
[TR]
[TD]01-May-2013[/TD]
[TD]$412.20[/TD]
[/TR]
[TR]
[TD]01-Jun-2013[/TD]
[TD]$412.20[/TD]
[/TR]
[TR]
[TD]01-Jul-2013[/TD]
[TD]$412.20[/TD]
[/TR]
[TR]
[TD]01-Aug-2013[/TD]
[TD]$412.20[/TD]
[/TR]
[TR]
[TD]01-Sep-2013[/TD]
[TD]$412.20[/TD]
[/TR]
[TR]
[TD]01-Oct-2013[/TD]
[TD]$412.20[/TD]
[/TR]
[TR]
[TD]01-Nov-2013[/TD]
[TD]$412.20[/TD]
[/TR]
[TR]
[TD]01-Dec-2013[/TD]
[TD]$412.20[/TD]
[/TR]
[TR]
[TD]01-Jan-2014[/TD]
[TD]$412.20[/TD]
[/TR]
[TR]
[TD]01-Feb-2014[/TD]
[TD]$316.21[/TD]
[/TR]
[TR]
[TD]01-Mar-2014[/TD]
[TD]$316.21[/TD]
[/TR]
[TR]
[TD]01-Apr-2014[/TD]
[TD]$316.21[/TD]
[/TR]
[TR]
[TD]01-May-2014[/TD]
[TD]$316.21[/TD]
[/TR]
[TR]
[TD]01-Jun-2014[/TD]
[TD]$316.21[/TD]
[/TR]
[TR]
[TD]01-Jul-2014[/TD]
[TD]$316.21[/TD]
[/TR]
[TR]
[TD]01-Aug-2014[/TD]
[TD]$316.21[/TD]
[/TR]
[TR]
[TD]01-Sep-2014[/TD]
[TD]$316.21[/TD]
[/TR]
[TR]
[TD]01-Oct-2014[/TD]
[TD]$316.21[/TD]
[/TR]
[TR]
[TD]01-Nov-2014[/TD]
[TD]$316.21[/TD]
[/TR]
[TR]
[TD]01-Dec-2014[/TD]
[TD]$298.37[/TD]
[/TR]
[TR]
[TD]01-Jan-2015[/TD]
[TD]$298.37[/TD]
[/TR]
</tbody>[/TABLE]


Obviously, it's not 2015 yet, but this formula is giving me values up through October 2016. My first guess is that it's reading the dates wrong somehow, but I can't figure it out. Any tips?

 

[Andrew Poulsom]

Welcome to MrExcel.

I think your formula should be:

=SUMIFS(Accounts!$F$4:$F$44444,Accounts!$A$4:$A$44444,">="&Budgeting!A3,Accounts!$A$4:$A$44444,"<"&Budgeting!A4,Accounts!$E$4:$E$44444,"="&Budgeting!$B$1)

 

[HawaiianShirts]

That did it! I guess I'm just not paying enough attention to my cell references...