A
Alan
Can anybody help me with the following problem. I’m creating a spreadsheet
that shows expenses for a business structure. The expenses are recorded as
they occur (in chronological order) and are classified into about 20
different categories. I want to be able to compile the data at the end of the
month and arrive at totals for each of the 20 categories. Using the if
function isn’t going to work here as the formula would be a hundred miles
long.
I need something like (in English): search D1
20 (column which shows the
expense category) for A40 (which could contain “accountingâ€, “motor vehicle
expense†etc) and sum the cells to the right (showing the value of the
expense) for each cell in D120 where the text in the cell is the same as
the text in A40. This would allow me to have a summary at the end showing the
total accounting cost or motor vehicle expense etc. I’ve tried using the
lookup and similar functions but they don’t seem to work when you’re using
text. I’m using Excel 2003.
For example
Column A Column B
Expense A 100
Expense B 10
Income 20
Expense A 1000
Income 400
I want to write a formula that will total all cells in column B that
correspond to “Expense A†in column A which would equal 100 + 1,000 = 1,100.
I’m sure there must be a way to do this but I’m running into a brick wall.
Note that there will be about 40 different expense types, so nesting if
functions isn’t going to be feasible.
Any help would be greatly appreciated.
Cheers,
Alan.
that shows expenses for a business structure. The expenses are recorded as
they occur (in chronological order) and are classified into about 20
different categories. I want to be able to compile the data at the end of the
month and arrive at totals for each of the 20 categories. Using the if
function isn’t going to work here as the formula would be a hundred miles
long.
I need something like (in English): search D1
20 (column which shows theexpense category) for A40 (which could contain “accountingâ€, “motor vehicle
expense†etc) and sum the cells to the right (showing the value of the
expense) for each cell in D1
20 where the text in the cell is the same asthe text in A40. This would allow me to have a summary at the end showing the
total accounting cost or motor vehicle expense etc. I’ve tried using the
lookup and similar functions but they don’t seem to work when you’re using
text. I’m using Excel 2003.
For example
Column A Column B
Expense A 100
Expense B 10
Income 20
Expense A 1000
Income 400
I want to write a formula that will total all cells in column B that
correspond to “Expense A†in column A which would equal 100 + 1,000 = 1,100.
I’m sure there must be a way to do this but I’m running into a brick wall.
Note that there will be about 40 different expense types, so nesting if
functions isn’t going to be feasible.
Any help would be greatly appreciated.
Cheers,
Alan.