How to add amount to a cell based on category and month

gabrielinlompoc · 02-10-2006, 06:35 PM

I have a list of expenxe categories and by month along the top. How can I
write the sumif function based on two criteria from drop-down list(one list
is Expenses and the second list is the Months) and the third (Amount) cell
would be where I input an amount to be added into the table:
Expenses month amount
Fuel JAN _______

Expenses JAN FEB MAR
Trucks/Auto
Fuel 348.23
Oils 35.99
Maintenance 298.33
Parts 59.21
DMV 745.87

--
so many functions...!?!?!?!?!?!?

flummi · 02-11-2006, 10:10 AM

If you are trying to fill a matrix of expenses down and months across
with data you type in 3 cells that won't work because any formulas in
the matrix will at all times reflect the input in your 3 cells unless
you have a copy of the matrix somewhere that only contains values.

So, your table contains the values of the copy table (by means of
formulas)
now input your data
the formulas will identify the field in your matrix to modify, take the
according value from the copy table, add the new amount, at which stage
you need to copy your table and paste-->special-->values it to the
other location.
Empty your input fields

start all over

It seems advisable to do the coyping and emptying by means of a macro
and a button.

Mind though that it is a fairly risky procedure since if you get
interrupted you would never know whether you copied or not. If you use
a button, however, there are ways to signal that.

example:

colour blue
month jan
amount 40
row 2

original copy
jan feb mrz jan feb mrz
green 15 9 7 15 9 7
blue 50 7 4 10 7 4
yellow 3 9 1 3 9 1

Formulas:

row (B4): =MATCH(B1;A8:A10;0)
Matrix (C8) copied to all other cells down and across:
=IF(AND($B$2=C$7;$B$4=ROW($C8)-ROW($C$7));G8+$B$3;G8)
You may have to replace the semicolons with commas depending on your
local Windows setting for regional and language

Hans

gabrielinlompoc · 02-12-2006, 10:00 PM

Ok, I don't think I explained myself better, sorry, my fault. At the top I
have a labeled drop-down list tied to the list of expense categories(Fuel,
Repairs etc...) next to it is another drop-down list tied to the months(JAN,
FEB, MAR, etc...) next to that is a cell labeled Amount with a cell below it
to input an Dollar amount for a maybe fuel receipt for the month of JAN and
it would add itself to that particluar cell. Then say the next receipt in
line is a receipt for a REPAIR for the month of FEB and it would atumatically
add itself to that cell intersecting REPAIR and FEB. I have seen some
questions similar but in reverse it seems on this site and I can't seem to
get it right. The cell intersecting an EXPENSE category and MONTH would
recognize an amount entered under AMOUNT and added to whatever was entered
previously. Something lif IF CATEGORY IS FUEL AND MONTH IS JAN THEN ADD
appropiate cell. I had something similar under access but I kinda needed to
be in excell and when I copy and try to past in excell it won't work. Thank
you Mr. flummi for your help.
--
so many functions...!?!?!?!?!?!?

flummi · 02-13-2006, 03:10 AM

Hi,

there's 2 ways to understand your design:

1. You have row 2 at the top to specify the type of expense, the month
and to enter a Dollar amount. Below that you have the expense
categories listed in a column say A4:A10 and the months listed across
say in B3:M3 and in e.g. B4:M10 (for 10 expense categories) you have
the accumulators for the expense amounts. Everytime you select a
category and a month and enter an amount in row 2 you want to add the
amount in the cell intersecting the expense category in A4:A10 and the
month in B3:M3.

This is what I described in my first post. It is not possible with
normal Excel formulas because in Excel you cannot have a formula like
=A3=A3+B4. This will result in an error message "circular reference".
The only sensible way to oranize this seems a command button with an
associated macro that, if the button is clicked, adds the input amount
to the correct cell in your expense matrix.

2. You have kind of a journal design. columns A, B and C hold your
input data per line. Columns D:O have the amount in Column C in the
correct month. like in this example:

Expenses table
jan feb mrz apr mai
category month amount
Fuel jan 45,00 45,00
Repair feb 112,50 112,50
hotel feb 245,00 245,00
tyres jan 256,00 256,00
Penalty mrz 70,00 70,00

Total 301,00 357,50 70,00

The formula in D4 is simple: =IF($B4=D$2;$C4;0)
copied down and across as required.

This means, when you have a new expense you enter it in a new row, copy
the formulas from the previous row into the new one and that's it. Any
sums you would display at the bottom. If you want the total amount per
Category and month use a different area on the same sheet or a
different sheet and extract the information from your "journal".

Does that make sense?

Hans

gabrielinlompoc · 02-13-2006, 04:45 PM

Mr. Flummi
thank you for answering my questions. yes, I was getting a "circular"
answear. it works in access, but I have to keep tables for every expense and
I wanted to see the table and also input. it's just much easier to set up in
excell and much faster to set up.
so many functions...!?!?!?!?!?!?

"flummi" wrote:

> Hi,
>
> there's 2 ways to understand your design:
>
> 1. You have row 2 at the top to specify the type of expense, the month
> and to enter a Dollar amount. Below that you have the expense
> categories listed in a column say A4:A10 and the months listed across
> say in B3:M3 and in e.g. B4:M10 (for 10 expense categories) you have
> the accumulators for the expense amounts. Everytime you select a
> category and a month and enter an amount in row 2 you want to add the
> amount in the cell intersecting the expense category in A4:A10 and the
> month in B3:M3.
>
> This is what I described in my first post. It is not possible with
> normal Excel formulas because in Excel you cannot have a formula like
> =A3=A3+B4. This will result in an error message "circular reference".
> The only sensible way to oranize this seems a command button with an
> associated macro that, if the button is clicked, adds the input amount
> to the correct cell in your expense matrix.
>
> 2. You have kind of a journal design. columns A, B and C hold your
> input data per line. Columns D:O have the amount in Column C in the
> correct month. like in this example:
>
> Expenses table
> jan feb mrz apr mai
> category month amount
> Fuel jan 45,00 45,00
> Repair feb 112,50 112,50
> hotel feb 245,00 245,00
> tyres jan 256,00 256,00
> Penalty mrz 70,00 70,00
>
> Total 301,00 357,50 70,00
>
> The formula in D4 is simple: =IF($B4=D$2;$C4;0)
> copied down and across as required.
>
> This means, when you have a new expense you enter it in a new row, copy
> the formulas from the previous row into the new one and that's it. Any
> sums you would display at the bottom. If you want the total amount per
> Category and month use a different area on the same sheet or a
> different sheet and extract the information from your "journal".
>
> Does that make sense?
>
> Hans
>
>