Excel formula
 

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?

Excel formula
 

Hi


"Rob" wrote in message
...
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?

Of-course there is! But those formulas depends on algorithms, the amount is
divided between specific expenses. I.e. there isn't enough information to
give you more detailed help.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )

Excel formula
 

On Jul 31, 12:02 am, Rob wrote:
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.