I am keeping a spreadsheet of my monthly spending. How do I add to a number
that is already in a cell? For example, in the category "groceries" I have a
total of $182and I want to add 47.00 to it. I tried just writing + 47 in the
fx line and I tried sum: but it didnt' work. I know this is something simple
but I'm stuck. Thanks.

You can't without using VBA. A cell contains either a formula or value but
not both.

Perhaps you need separate "summation" categories so individual items
classified as "groceries" (and other groupings) are totalled in a "groceries"
total.

"Roberta" wrote:

I am keeping a spreadsheet of my monthly spending. How do I add to a number
that is already in a cell? For example, in the category "groceries" I have a
total of $182and I want to add 47.00 to it. I tried just writing + 47 in the
fx line and I tried sum: but it didnt' work. I know this is something simple
but I'm stuck. Thanks.

A couple of suggestions and a possible solution. First - as Toppers has
said, you can either have a formula or a value in a cell. If your current
total cell has something like
=182
in it, then you either change that to = 229 or to = 182+47
or if it has cell references like = C3 + C9 + C10 and your 47 is in C15 you
have to modify it to =C3+C9+C10+C15
That becomes tiresome and certainly doesn't make full use of the
capabilities of Excel.

Suggestion #1: put your totals for categories at the TOP of your worksheet -
they'll always be in ready view and modifying formulas to them will be easier
in the long run.
Suggestion #2: you probably already have this, but if not, you should ... in
the individual budget entries down the sheet, you should have a column for
your categories and you should be consistent in the entries in it. Consider
creating a list of valid categories and then using Data Validation in the
cells in that column so you can pick from the list, guaranteeing that the
entries are consistent.

Now, if you follow suggestion #2 you can set up some formulas that will need
very little maintenance in your totals section, whether at the top or bottom
of your sheet.

Lets say up in the top you have places to show the total expenses in the
categories: you have a cell that has the name of the category and next to it
you have a cell where you want to see the total right next to it (lets say
category name 'Groceries' is in A1, and you want the total for that category
in B1 next to it).

In your data entries section down further on the worksheet, the categories
are in column C, just to keep column letters separate and explanations
clearer. The amount of an expense in one of those 'daily' entries is in
column D. Those entries start at row 10 and go on down the sheet from there.

In cell B1 you could put a formula like this:
=SUMPRODUCT(--(C10:C100=A1),D10:100)
That will give you a total amount of all entries in the daily entries for
category Groceries, and it will update automatically as you add more entries
to the sheet.

You can set the same formula up for other categories: the formula would
always be the same except for the A1 reference - that will change to the cell
with the name of the category in it in the totals section of the worksheet.

The only thing you have to watch out for is making entries beyond the end of
the range for C and D in the formula - you'll need to check on that from time
to time and make it a larger number, such as C10:C150 and D10:D150. You can
initially include a good sized number of additional, empty rows without harm.
Making it really large can affect performance of the worksheet. Although
you could set them to C10:C65535 and D10:D65535 and never have to worry about
changing them again (65536 is max row number in Excel 2003). But as I said,
there'll be a performance hit for having to look at all those rows even
though most will remain empty.

"Roberta" wrote:

I am keeping a spreadsheet of my monthly spending. How do I add to a number
that is already in a cell? For example, in the category "groceries" I have a
total of $182and I want to add 47.00 to it. I tried just writing + 47 in the
fx line and I tried sum: but it didnt' work. I know this is something simple
but I'm stuck. Thanks.

=SUMPRODUCT(--(C10:C100=A1),D10:100)
you could set them to C10:C65535 and D10:D65535
there'll be a performance hit for having to look at all
those rows even though most will remain empty.

Use SUMIF instead:

=SUMIF(C:C,A1,D:D)

--
Biff
Microsoft Excel MVP


"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
A couple of suggestions and a possible solution. First - as Toppers has
said, you can either have a formula or a value in a cell. If your current
total cell has something like
=182
in it, then you either change that to = 229 or to = 182+47
or if it has cell references like = C3 + C9 + C10 and your 47 is in C15
you
have to modify it to =C3+C9+C10+C15
That becomes tiresome and certainly doesn't make full use of the
capabilities of Excel.

Suggestion #1: put your totals for categories at the TOP of your
worksheet -
they'll always be in ready view and modifying formulas to them will be
easier
in the long run.
Suggestion #2: you probably already have this, but if not, you should ...
in
the individual budget entries down the sheet, you should have a column for
your categories and you should be consistent in the entries in it.
Consider
creating a list of valid categories and then using Data Validation in the
cells in that column so you can pick from the list, guaranteeing that the
entries are consistent.

Now, if you follow suggestion #2 you can set up some formulas that will
need
very little maintenance in your totals section, whether at the top or
bottom
of your sheet.

Lets say up in the top you have places to show the total expenses in the
categories: you have a cell that has the name of the category and next to
it
you have a cell where you want to see the total right next to it (lets say
category name 'Groceries' is in A1, and you want the total for that
category
in B1 next to it).

In your data entries section down further on the worksheet, the categories
are in column C, just to keep column letters separate and explanations
clearer. The amount of an expense in one of those 'daily' entries is in
column D. Those entries start at row 10 and go on down the sheet from
there.

In cell B1 you could put a formula like this:
=SUMPRODUCT(--(C10:C100=A1),D10:100)
That will give you a total amount of all entries in the daily entries for
category Groceries, and it will update automatically as you add more
entries
to the sheet.

You can set the same formula up for other categories: the formula would
always be the same except for the A1 reference - that will change to the
cell
with the name of the category in it in the totals section of the
worksheet.

The only thing you have to watch out for is making entries beyond the end
of
the range for C and D in the formula - you'll need to check on that from
time
to time and make it a larger number, such as C10:C150 and D10:D150. You
can
initially include a good sized number of additional, empty rows without
harm.
Making it really large can affect performance of the worksheet. Although
you could set them to C10:C65535 and D10:D65535 and never have to worry
about
changing them again (65536 is max row number in Excel 2003). But as I
said,
there'll be a performance hit for having to look at all those rows even
though most will remain empty.

"Roberta" wrote:

I am keeping a spreadsheet of my monthly spending. How do I add to a
number
that is already in a cell? For example, in the category "groceries" I
have a
total of $182and I want to add 47.00 to it. I tried just writing + 47 in
the
fx line and I tried sum: but it didnt' work. I know this is something
simple
but I'm stuck. Thanks.

Very good recommendation - saves her maintenance 'fees'! <g Along with a
reduced performance hit.

"T. Valko" wrote:

=SUMPRODUCT(--(C10:C100=A1),D10:100)
you could set them to C10:C65535 and D10:D65535
there'll be a performance hit for having to look at all
those rows even though most will remain empty.

Use SUMIF instead:

=SUMIF(C:C,A1,D:D)

--
Biff
Microsoft Excel MVP


"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
A couple of suggestions and a possible solution. First - as Toppers has
said, you can either have a formula or a value in a cell. If your current
total cell has something like
=182
in it, then you either change that to = 229 or to = 182+47
or if it has cell references like = C3 + C9 + C10 and your 47 is in C15
you
have to modify it to =C3+C9+C10+C15
That becomes tiresome and certainly doesn't make full use of the
capabilities of Excel.

Suggestion #1: put your totals for categories at the TOP of your
worksheet -
they'll always be in ready view and modifying formulas to them will be
easier
in the long run.
Suggestion #2: you probably already have this, but if not, you should ...
in
the individual budget entries down the sheet, you should have a column for
your categories and you should be consistent in the entries in it.
Consider
creating a list of valid categories and then using Data Validation in the
cells in that column so you can pick from the list, guaranteeing that the
entries are consistent.

Now, if you follow suggestion #2 you can set up some formulas that will
need
very little maintenance in your totals section, whether at the top or
bottom
of your sheet.

Lets say up in the top you have places to show the total expenses in the
categories: you have a cell that has the name of the category and next to
it
you have a cell where you want to see the total right next to it (lets say
category name 'Groceries' is in A1, and you want the total for that
category
in B1 next to it).

In your data entries section down further on the worksheet, the categories
are in column C, just to keep column letters separate and explanations
clearer. The amount of an expense in one of those 'daily' entries is in
column D. Those entries start at row 10 and go on down the sheet from
there.

In cell B1 you could put a formula like this:
=SUMPRODUCT(--(C10:C100=A1),D10:100)
That will give you a total amount of all entries in the daily entries for
category Groceries, and it will update automatically as you add more
entries
to the sheet.

You can set the same formula up for other categories: the formula would
always be the same except for the A1 reference - that will change to the
cell
with the name of the category in it in the totals section of the
worksheet.

The only thing you have to watch out for is making entries beyond the end
of
the range for C and D in the formula - you'll need to check on that from
time
to time and make it a larger number, such as C10:C150 and D10:D150. You
can
initially include a good sized number of additional, empty rows without
harm.
Making it really large can affect performance of the worksheet. Although
you could set them to C10:C65535 and D10:D65535 and never have to worry
about
changing them again (65536 is max row number in Excel 2003). But as I
said,
there'll be a performance hit for having to look at all those rows even
though most will remain empty.

"Roberta" wrote:

I am keeping a spreadsheet of my monthly spending. How do I add to a
number
that is already in a cell? For example, in the category "groceries" I
have a
total of $182and I want to add 47.00 to it. I tried just writing + 47 in
the
fx line and I tried sum: but it didnt' work. I know this is something
simple
but I'm stuck. Thanks.

One way: enter 47 in a blank cell, then go to that cell and copy the
47. Go to the cell where the $182 is and click on edit-paste special
values-add-ok

On Sat, 11 Aug 2007 12:14:02 -0700, ?B?Um9iZXJ0YQ==?=
wrote:

I am keeping a spreadsheet of my monthly spending. How do I add to a number
that is already in a cell? For example, in the category "groceries" I have a
total of $182and I want to add 47.00 to it. I tried just writing + 47 in the
fx line and I tried sum: but it didnt' work. I know this is something simple
but I'm stuck. Thanks.