Conditional SUM Wizard does not work with dynamic list.

[LMcQ]

Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I'm making a budget, and I want to use a list to input expenses. The expense list will be ever-changing as new expenses are put in (I used the list wizard), and I want to be able to have an overview sheet that stays updated with the different expense categories.
Conditional Sum only seems to work with an unchanging list, so the minute I put new expenses in, the overview sheet is outdated.

Any suggestions or alternative approaches anyone can suggest?

 

[Bob Greenblatt]

Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I'm
making a budget, and I want to use a list to input expenses. The expense list
will be ever-changing as new expenses are put in (I used the list wizard), and
I want to be able to have an overview sheet that stays updated with the
different expense categories.
Conditional Sum only seems to work with an unchanging list, so the minute I
put new expenses in, the overview sheet is outdated.

Any suggestions or alternative approaches anyone can suggest?

By conditional sum, (I assume you mean SUMIF) works with any properly
defined range, and certainly DOES work with a dynamic list.

For example, if your expense category is in column A and the amount is in
column B, then a formula like the following will work fine as the list
changes:

=sumif(offset($A$1,0,0,counta($a:$A),1),²Rent²,offset($b$1,0,0,counta($a:$a)
,1))

Adjust the dynamic range expressions above if your list is different. Post
back with more details of your data and what you have tried if you are
having trouble.

 

[LMcQ]

Thanks, but that didn't seem to work when I plugged in my values....perhaps I did something wrong.

I used the list wizard to create a list, so the list grows on the bottom. Today there are 10 total rows, and in a few days there may be 20 rows in the list. Row 1 is the category row.

The value option for column C is a drop down list with the expense category, for example, groceries. The value option for column D is currency, which is what I want to add.
When I modified your formula to apply to my list, then plugged it into a random cell on the sheet, it told me the formula refers to empty cells.
Thanks again!

 

[Bob Greenblatt]

Thanks, but that didn't seem to work when I plugged in my values....perhaps I
did something wrong.

I used the list wizard to create a list, so the list grows on the bottom.
Today there are 10 total rows, and in a few days there may be 20 rows in the
list. Row 1 is the category row.

The value option for column C is a drop down list with the expense category,
for example, groceries. The value option for column D is currency, which is
what I want to add.
When I modified your formula to apply to my list, then plugged it into a
random cell on the sheet, it told me the formula refers to empty cells.
Thanks again!

The formula certainly does refer to empty cells. But it will work fine. Did
you try it? Did it work? If not, what happened?

 

[LMcQ]

Wonderful.
I had a bit of a hard time making it work (I'm an excel beginner), but I learned about offset and counta by playing with your formula.
Thank you!