Can anybody help me with the following problem. Im 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 isnt going to work here as the formula would be a hundred miles
long.
I need something like (in English): search D1:D20 (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 D1:D20 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. Ive tried using the
lookup and similar functions but they dont seem to work when youre using
text. Im 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.

Im sure there must be a way to do this but Im running into a brick wall.
Note that there will be about 40 different expense types, so nesting if
functions isnt going to be feasible.

Any help would be greatly appreciated.

Cheers,

Alan.