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.

Try the below formula in Cell D1 and copy down to get the below result...
=SUMIF(A:A,C1,B:B)

Col A Col B Col C Col D
Expense A 100 Expense A 1100
Expense B 10 Expense B 10
Income 20 Income 420
Expense A 1000
Income 400
-----
-----

If this post helps click Yes
---------------
Jacob Skaria


"Alan" wrote:

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.

Try this...

A1:A5 = categories
B1:B5 = values to sum

D1:Dn = list of unique categories

Enter this formula in E1:

=SUMIF(A$1:A$5,D1,B$1:B$5)

Copy down as needed.

--
Biff
Microsoft Excel MVP


"Alan" wrote in message
...
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: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. 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.

Another option would be to create a pivot, with col A's header placed in ROW
area, col B's header in DATA (set to SUM). The pivot will return both the
list of unique items (in col A) and the corresponding sums (from col B)
adjacent to it. Just a couple of seconds, and its done. Good to know, even if
you prefer the formulas route. Any worth? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Alan" wrote:
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.

Hey Guys,
Thanks for the replies. Don't know how I'd overlooked the sumif function
(i've used it before) and it works perfectly for what I'm trying to acheive.
Great forum and thanks for the input.

Cheers,

Al.

"Max" wrote:

Another option would be to create a pivot, with col A's header placed in ROW
area, col B's header in DATA (set to SUM). The pivot will return both the
list of unique items (in col A) and the corresponding sums (from col B)
adjacent to it. Just a couple of seconds, and its done. Good to know, even if
you prefer the formulas route. Any worth? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Alan" wrote:
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.