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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

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...
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,263
Messages
5,769,117
Members
425,518
Latest member
seothaeng

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