I am doing a check register for myself in excel. I am inputing the name of
the payee and the amount in seperate columns. I am setting up columns with
specific expenses and need amounts to flow to these columns. Is there a
formula that i can use that will pull these amounts into the specific expense
columns?
The details would depend on the specific design of your spreadsheet.
I would do something like the following....
For your data entry, have a column in which you specify the expense
category next to the payee name and amount columns. For example,
suppose B2:B100 contain the amounts, and C2:C100 contain the category
for each expense.
For your summary expense column(s) -- probably in a new worksheet
(Sheet2) -- you might have the following:
column A: category name
B2: =sumproduct((Sheet1!$C$2:$C$100=A2)*(Sheet1!$B$2:$B$100))
Copy-and-paste or drag B2 down for each category.
Useful trick.... Note that we must specify the Sheet 1 ranges
explicitly. We cannot use C:C and B:B, I believe. Make sure row 2
and row 100 in Sheet1 have blank columns, and always add new expenses
by using Insert Row below row 2 or above row 100. Then the ranges in
the formulas above will be updated automatically.
PS: The SUMPRODUCT above is equivalent to the following:
B2: =sumproduct(--(Sheet1!$C$2:$C$100=A2), Sheet1!$B$2:$B$100)
I don't know if there is any technical benefit of one form over the
other (e.g. efficiency). But I doubt that your spreadsheet would
become so complex that you would notice any difference. Many people
are confused by the "--(...)" notation, so I tend to avoid it.
