Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
Build excel formula using field values as text in the formula | Excel Worksheet Functions | |||
Excel 2002 formula displayed not value formula option not checked | Excel Worksheet Functions | |||
How do I view formula results intead of formula in excel? | Excel Worksheet Functions | |||
i edit a formula (excel) then it displays formula not answer | Excel Discussion (Misc queries) |