# 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

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

#### 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
211
Replies
0
Views
114
Replies
14
Views
372
Replies
5
Views
84
Replies
3
Views
267

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.

### Which adblocker are you using?

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