Numbers ?: Formula will not let me select cells, only whole column

[ejy5]

Hello everyone-
I'm new to iworks and have trying to figure this one out for a while. I used a template to set up financial info and calculate how much was spent on items. This was the preset formula:

=SUMIF(Transactions :: $Category,A2,Transactions :: Amount)

However, I only want the sum from a particular range of dates (e.g. 9/1/09 to 9/30/09, as in cells B2:B10) so I can budget accordingly. In excel, you just grab the highlighted corner and drag it to where you want- here I can't do that and it won't let me add any more "conditions". Any idea how to remedy this? It's driving me nuts.

On a side note, I am NOT a programmer so programming language will unfortunately not help unless you get VERY specific about how to do it (which you probably don't want to do).

 

[mysterytramp]

Just a couple of questions:

1) I assume you're using the "Checking Account" template, correct?
2) Do you want just a monthly total of expenses, or do you want a monthly total of expenses divided by category? The first is pretty easy, the second is a little trickier.

mt

 

[mysterytramp]

Supposing you're going to want to to have a matrix of expense categories and months ...

What you're probably going to want to do is first, create a new column in the table called "Transactions." If you haven't changed the template, it's going to be Column G. And in G2, type, "Month." And in G3 (the cell right below it), type:

=Month(B3)

You should see a number the corresponds to the number of the month of the year. October = 10, November = 11, etc. You'll need to copy this for every transaction you've recorded, so select G3, and select that column down to the last transaction. Then use the Fill Down command in the Insert menu.

Now, if you want, you can hide this column.

To make the matrix, I enlarged the Account Categories table to 13 columns, one column on the left for the categories, then one column for each month. In cell B1 where it said "Amount", you can type "January" then select the 12 cells to the right. Use the Fill Right command in the Insert menu, and it will fill in all the months for you.

Directly below January, in cell B2, enter this formula:

=SUMIFS($Amount, $Category,$A2,$Month,COLUMN()-1)

Once the formula is accepted -- you should see bubbles around Amount, Category and Month -- make sure Amount, Category and Month are set for "Absolute start and end."

You find that out by mousing over the bubble and you'll see an inverted triangle to the right of the bubble. Select the bubble and a small menu appears with four options. You want Amount, Category and Month to be absolute start and end.

For A2, however, it should be Absolute Column.

Now, select cell B2 through the cells below corresponding to your categories. Use the Fill Down command.

Now select those cells, and the cells to the right corresponding to each month. Use the Fill Right command.

You should see lots of "$ 0.00" and maybe some numbers for where you've been entering data.

mt

Full Disclosure ... I think there should be a way for the SUMIFS command to take data from a calculated date, but I came up with zilch. So that's why you have your hidden "Month" column.