Sum in a register

**Trisarahtops** · 10-03-2014, 08:19 PM

Hello!

So I am pretty new with working excel, so I miss a lot of straight forward things. Any way, I have started this project to, well, not only have the project, but kind of learn how to better use excel.
So my project is to make a check register. So far I have the register that records costs and deposits and all of the standard function. Now I want to make it so that I can sum up different types of costs. Kind of like budgeting. So say I spend money on food, I check a box and it adds it to my food spend total. I know you can do that but I am not sure how to. Any suggestions?
Thanks!

**6StringJazzer** · 10-03-2014, 08:57 PM

There is not just one way to do this. Here is how I would do it, though. See attached.

This model uses two intermediate techniques that you might not be familiar with:

1. The dynamic named range. In Excel you can name a range. For example, you can give the range A1:A10 a name, like Series. Then when you would use

=SUM(A1:A10)

you can use

=SUM(Series)

To make a named range dynamic, you can use a formula like this:

=OFFSET(Categories!$A$2,0,0,COUNTA(Categories!$A:$A)-1,1)

This formula returns a range of all non-blank items in column A starting in row 2. This is how I created the category list.

2. Data validation. You can specify a list of data to choose from in a cell. I used this named range as the data list, so you can pick a category from a dropdown list.