SUMIFS Forumla with Date Range Returning Unexpected Values

HawaiianShirts

New Member
Joined
Jul 19, 2014
Messages
15
Office Version
  1. 365
Platform
  1. Windows
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:

Date (Column A)Other Details (Columns B-D)Category (Column E)Amount (Column F)
04-May-2014DetailsGroceries104.65
10-Jun-2014DetailsUtilities74.95

<tbody>
</tbody>

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

Date (Column A)Groceries (Column B)Utilities (Column C)Other Categories (D, E, F, etc.)
01-May-2014=sumifs=sumifs=sumifs
01-Jun-2014=sumifs=sumifs=sumifs

<tbody>
</tbody>

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.

Category:Groceries
Dates(Blank)
01-May-2013$412.20
01-Jun-2013$412.20
01-Jul-2013$412.20
01-Aug-2013$412.20
01-Sep-2013$412.20
01-Oct-2013$412.20
01-Nov-2013$412.20
01-Dec-2013$412.20
01-Jan-2014$412.20
01-Feb-2014$316.21
01-Mar-2014$316.21
01-Apr-2014$316.21
01-May-2014$316.21
01-Jun-2014$316.21
01-Jul-2014$316.21
01-Aug-2014$316.21
01-Sep-2014$316.21
01-Oct-2014$316.21
01-Nov-2014$316.21
01-Dec-2014$298.37
01-Jan-2015$298.37

<tbody>
</tbody>


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?
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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)
 
Solution

HawaiianShirts

New Member
Joined
Jul 19, 2014
Messages
15
Office Version
  1. 365
Platform
  1. Windows
That did it! I guess I'm just not paying enough attention to my cell references...
 

Forum statistics

Threads
1,186,164
Messages
5,956,309
Members
438,246
Latest member
Deelea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top