Need formula for a macro function

[Cata_bRc]

Hy all
I have an excel document with 31 sheets (one sheet for every day of this mounth)
In every sheet I have a table with 2 field: name and value
I have 3 names Name1, Name2 and Name 3 (I can have multiple Name1, multiple Name2, multiple Name3)
At the end I have another sheet: Total
How can I have in the sheet Total the total value for Name1, total value for Name 2, total value for Name3 calculate from the all sheets that I have
Thank you for all your help




P.S. sorry for my english

[Domski]

Hi,

The easiest way (other than keeping all your data on one worksheet which would be recommended) would be to do a SUMIF formula in the same cell on each worksheet and then a 3D SUM formula on your Total sheet adding up those cells =SUM('1:31'!A1)

Dom

[inesi]

Cata_bRc, your english is fine, don't worry.

One way you can do that is by using the formula SUMIF. For example:
1) Let's consider you have three sheets (Sheet1, Sheet2 and Sheet3) and a 4th one called Total

2) On Sheet1 you have:
Name | Value
ABC | 10
ABC | 20
DEF | 30
GHI | 40

3) On Sheet2 you have:
Name | Value
ABC | 10
DEF | 20
GHI | 30


4) On Sheet3 you have:
Name | Value
DEF | 10
GHI | 20

Then, on sheet Total you can do the following:
Name | Value
ABC | =SUMIF(Sheet1!A:A,A2,Sheet1!B:B)+SUMIF(Sheet2!A:A,A2,Sheet2!B:B)+SUMIF(Sheet3!A:A,A2,Sheet3!B:B)
DEF | =SUMIF(Sheet1!A:A,A3,Sheet1!B:B)+SUMIF(Sheet2!A:A,A3,Sheet2!B:B)+SUMIF(Sheet3!A:A,A3,Sheet3!B:B)
GHI | =SUMIF(Sheet1!A:A,A4,Sheet1!B:B)+SUMIF(Sheet2!A:A,A4,Sheet2!B:B)+SUMIF(Sheet3!A:A,A4,Sheet3!B:B)

As you can notice that way will required one SUMIF for each sheet on your workbook.

As an alternative you could put all 31 Sheets in a single one (columns: Day, Name, Value) and use a very powerful Excel tool called Pivot Table.

[Domski]

Here's any example workbook.

Dom

[Cata_bRc]

Thank you very very much!
You have been very helpful!