Sum similar concepts found in different tabs

**hochedez** · 03-28-2005, 09:40 AM

Hello,

I need a little help, I really can't come up with a solution to that little problem:

I have 4 excel sheets (same file) containing a bunch of lines. The columns in these sheets are:

SUP-CODE / DPT / SUP NAME / CONCEPT/ INVOICE / SUM...

I have a 5th sheet that I wish would contain a summary of the other 4 sheets.
Basically, I need a formula that return a sum by concept (from the data from sheet 1-4)

CONCEPT1 = $$$$
CONCEPT2 = $$$$
etc.

I could simply put all of them in the same sheet and use a filter tool to get that result, but I really need a more permanent function (that would also allow me to add lines in the different sheets and take them into account).

I hope it's clear. Any help (even a quick tip to follow) will be greatly appreciated

Benoit

**swatsp0p** · 03-28-2005, 12:54 PM

I have to assume that the columns are all the same on each of your four sheets, that is, for example, DPT is in Column B on each sheet.

on your fifth sheet, enter this formula:

=SUM(Sheet1:Sheet4!B2:B10000)

Adjust the sheet names if your sheets are named and the range of the data as needed.

Repeat for your other columns.

HTH

Bruce

**hochedez** · 03-28-2005, 11:10 PM

Sorry I guess i wasn't clear enough.

Yes the columns are the same exactly in my 4 sheets.

But what i want to do is a sum by concept:

(A) SUP-CODE / (B) DPT / (C) SUP NAME / (D) CONCEPT/ (E) INVOICE / (F) SUM...
----------------------------------------------------------------------------------------------------
11111 / 33 / MSOFT / REBATES / 111222333 / $2200
22222 / 40 / ADBOR / LEAFLET / 222172299 / $2400
33333 / 32 / ABC / REBATES / 333333333 / $2300

This is a sample of the tables i have in sheets 1-4

I would like to sum them by concept.

(A) CONCEPT / (B) SUM
REBATES / $$$$$ <--- need a formula here
LEAFLET / $$$$$
...

Any idea?

Ben

**Domenic** · 03-28-2005, 11:22 PM

Try...

B2, copied down:

=SUMPRODUCT(SUMIF(INDIRECT("'"&$D$2:$D$5&"'!D:D"),A2,INDIRECT("'"&$D$2:$D$5&"'!F:F")))

...where A2 contains the 'concept' of interest, and D2:D5 contains your sheet names.

Hope this helps!