Back to post 12 (and your screenshot):
I'm trying to make a personal finance spreadsheet. Each account (checking, savings, credit card, etc.) will be on a separate worksheet and will have all transactions categorized. On a separate page, I will reference the data across all accounts in order to show expenses by category per month. From there I can create charts.
1) It would be simpler to put data on one tab (if possible). If each tab represent a bank account (say, for example, cash, savings, checking, credit card) that's manageable but you have to do everything four times to roll it all up.
2) I see no need at all to use Columns() or Rows() in any formula. This was where your post started but it's unnecessarily complicated. You can do all of this with simple sumproducts (you don't even need array-entered formulas). This all may be slowing you down terribly, especially if your referencing tens of thousands of rows.
3) Use dynamic range names - this is a simple way to account for growing data.
4) Use accounts (numbers, or at least names) for all your expenses, if you aren't doing that already.
5) I like to use an end of month value in each record. Then you can match on these dates to roll up the period totals by account.
Here's a sample of what I mean:
<img alt="worksheet" src="http://northernocean.net/etc/mrexcel/20110716_wksht3.png" />
Formula in Cell H4 (Rollup expenses, with dynamic named ranges) (can be on another sheet, btw):
=SUMPRODUCT(--(Expenses=$G4),--(Periods=H$3),Amounts)
Formula in Cell E4 (month end dates):
=DATE(YEAR(D4),MONTH(D4)+1,0)
The rollup can be on a separate sheet. If you still have a tab for cash, checking, savings, credit card, you'll need 4 rollups, one for each. Then a final rollup of the rollups.
Note that although I've only rolled up expenses, the exact same table could be created to work out the period totals for the bank accounts (checking, saving, etc) just as was done with the expenses - all you would do is reference column A rather than column B.
Sample File (very simple but has the above tables in it):
<a href="http://northernocean.net/etc/mrexcel/20110716_book1.zip">Sample Workbook</a>
sha256 checksum: 76a4fcebf87815881ee917f2104474b2fbe4e80af21cc1aba6ee667158b9c42d
Just my two cents to try to clarify. There really is no need for and Rows() or Columns() or even array-entered formulas here - that is slowing you down and complicating things. Budget worksheets can have many wrinkles but just starting out with a table of expenses by period should not be as hard as it's getting here.
edit: ah, just noticed I have rent listed twice in the rollup ... ah well. Spreadsheets have errors like dogs have fleas. Its a reminder that that list should be your account list, with no duplicates and no omissions ...
