Sleeper: Compare and Calculate the Sum

[ermis1975]

I have 3 sheets with many columns each. I want in another sheet4 to take the sum of the values in the cells that the value in the first column is the same.
Eg.
Sheet1 Sheet2 Sheet3 sheet4

tom 5 peter 5 tom 4 tom 9
marc 7 Denis 3 peter 7 peter 12
D 4 p 9 u 7 u7
d4
denis 3

thanks

[Sir Babydum GBE]

Hello,

I'm not sure I understand your question properly.

Looking at your example, on your first line I can see why tom's value is 9 on sheet four - but I cannot see why Peter's sheet four result is 12. Why 12?

Also assuming that your names appear on A1 of each of the sheets, and the values appear in B1, what would appear in A1 of sheet four? How would it know to pick Tom - as your example suggests?

It might be helpful to rephrase your question a little, or to attach a zipped version of your workbook.

Thanks

Edit: Oh, I think I see what you mean now. Back in a minute...

[mdmackillop]

Hi Ermis,
It's always easier if you can attach a spreadsheet showing your data etc. as it saves the reponder from having to create one based on his/her understanding of your question. As you can see in your own posting, it is not easy to get a row/column layout in this format.

[Sir Babydum GBE]

Ok, if i may use another example - you can tell me whether I have understood you properly. Lets say that the name "Peter" appears 100 times in total in various places on sheets 1 to 3. The figure next to each occurence of Peter is "1"

Are you asking for code to create a single entry for "Peter" on sheet4 (say in cell A1), then to return the result "100" next to that entry (B1)?

I would know how to do this with a formula - but you would need to manually input the names in sheet4 first. If it's code you're after (after all, this is a VBA site), then the other guys here will probably be able to give you some pointers.

HTH

Babydum

[ermis1975]

ok I'll attach the folowing eg.
I want Sheet4 to be completed automaticaly calculating the values of each
cell in the 3 other sheet3 (matching the name).

[Sir Babydum GBE]

OK,

A bit long-winded, but it works:

=SUMIF(Sheet1!A$3:A$7,A2,Sheet1!D$3:D$7)+SUMIF(Sheet2!A$3:A$7,A2,Sheet1!D$3 :D$7)+SUMIF(Sheet3!A$3:A$7,A2,Sheet1!D$3:D$7)

I've attached an example with it on.

:)

[ermis1975]

tHANKS.... but what about completing all names in sheet4 automaticaly?

[Sir Babydum GBE]

erm... over to he coding experts...

[Bob Phillips]

tHANKS.... but what about completing all names in sheet4 automaticaly?

What do you mean by complete automatically. You have always said there will be 3 sheets, that formula will sum them, even when they are updated?

[ermis1975]

i MEAN to comlete the sheet4 with names and tha sum of values...

[Bob Phillips]

tHANKS.... but what about completing all names in sheet4 automaticaly?

i MEAN to comlete the sheet4 with names and tha sum of values...

Doesn't really enlighten me.

[ermis1975]

Thanks....anyway.

[Sir Babydum GBE]

hi xld

I think ermis means that sheet 4 is blank, but that the code would replicate the different names that appear on sheets 1 to 3, and then do the sum. I guess the names could change from week to week or something, and ermis is looking for a quick way to summarise the whole thing on sheet 4.

That's my take anyway.

[ermis1975]

Thats right... thanks

[Bob Phillips]

hi xld

I think ermis means that sheet 4 is blank, but that the code would replicate the different names that appear on sheets 1 to 3, and then do the sum. I guess the names could change from week to week or something, and ermis is looking for a quick way to summarise the whole thing on sheet 4.

That's my take anyway.

Hi Taff,

Well, why didn't they say so?

Here is a first cut, dumping the formula in H10.

Const sFormulaPart1 As String = "SUMIF("
Const sFormulaPart2 As String = "!A$3:A$7,A2,"
Const sFormulaPart3 As String = "!D$3:D$7)"
Dim sFormula As String
Dim sh As Worksheet
Worksheets("Sheet4").Activate
   sFormula = "="
    For Each sh In ActiveWorkbook.Worksheets
        If sh.Name <> ActiveSheet.Name Then
            sFormula = sFormula & sFormulaPart1 & _
                       sh.Name & sFormulaPart2 & _
                       sh.Name & sFormulaPart3 & "+"
        End If
    Next sh
ActiveSheet.Range("H10").Formula = Left(sFormula, Len(sFormula) - 1)
End Sub