Formula to combine cells (A challenge!)

[Guest]

Hi,

tried my best to find the answer through the HELP menu and
the knowlege base...but unsucessfull.

I have an expense tracking Excel file, with worksheets for
every months. Each worksheet has the same format.
Column A has a controlled list of expense type (Office
Supplies, Entertainment, Telehpone...etc). Column B has
the amount. Each row is an expense. Here is an example:

- Worksheet 1 (Jan 04)
COLUMN A COLUMN B
Telephone $245
Rent $500
Gaz $46
Office Exp. $456

- Worksheet 2 (Feb 04)
COLUMN A COLUMN B
Telephone $233
Rent $500
Gaz $12

etc....

I need to create a worksheet that resumes all the expenses
per category. That means that I need to write a formula
for each expense type that does the following, for
exmaple: Find in all the worksheets, in Column A a value
equal to "Telephone", get the value on the same row in
column B and calculate the total for this category only.

Therfore, on the first worksheet it will resume all my
expenses for year in each category.

I thank anyone in advance for his ingenious help !.

Regards.

 

[Norman Harker]

Hi!

As long as the items are in the same cell in each sheet and there are
no non-standard sheets in between:

=SUM('first sheet name:last sheet name'!A1)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Anders S]

Hi,

If the expense types are on the same rows in each monthly sheet, you can try
=SUM('First Sheet:Last Sheet'!B2)
in the summary sheet and fill down.
You will have to substitute "First Sheet" and "Last Sheet" with your actual worksheet names.

Otherwise I think you need a macro.

HTH
Anders Silven

 

[GT]

thanks for your help.
Actually your formula will combine all expenses category...I want to separate per category.
The total expenses for all worksheets in the TELEPHONE category.
The total expenses for all worksheets in the GAZ category.
etc...

I ahve the impression I need a macro.

 

[GT]

thanks for your help.
Actually your formula will combine all expenses category...I want to separate per category.
The total expenses for all worksheets in the TELEPHONE category.
The total expenses for all worksheets in the GAZ category.
etc...

I ahve the impression I need a macro.

 

[Norman Harker]

Hi GT!

The cell that you should refer to is the cell that contains the amount
for the category.

If you have more than one amount in a category on the sheets then I'd
recommend setting up totals within each sheet for each category using
SUMIF. Then you can add the items in those summaries as long as your
totalling section has the same matrix throughout.

You might use a more complex formula or VBA approach, but this looks
like the simplest way. KISS.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.