HawaiianShirts
New Member
- Joined
- Jul 19, 2014
- Messages
- 15
- Office Version
- 365
- Platform
- 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:
<tbody>
</tbody>
In the Budgeting sheet, Column A has dates and columns B through whatever will be for each expense category. Like so:
<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.
<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?
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?
