SUMIFS Forumla with Date Range Returning Unexpected Values

HawaiianShirts

New Member
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-2014 Details Groceries 104.65 10-Jun-2014 Details Utilities 74.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

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
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

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

Replies
3
Views
452
Replies
5
Views
236
Replies
7
Views
211
Replies
0
Views
144
Replies
1
Views
108

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.

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.

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

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