PDA

View Full Version : Sleeper: Compare and Calculate the Sum



ermis1975
07-05-2005, 02:54 PM
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
07-05-2005, 03:16 PM
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
07-05-2005, 03:35 PM
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
07-05-2005, 04:14 PM
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
07-05-2005, 11:56 PM
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
07-06-2005, 01:05 AM
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
07-06-2005, 01:14 AM
tHANKS.... but what about completing all names in sheet4 automaticaly?

Sir Babydum GBE
07-06-2005, 01:45 AM
erm... over to he coding experts...

Bob Phillips
07-06-2005, 02:46 AM
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
07-06-2005, 05:37 AM
i MEAN to comlete the sheet4 with names and tha sum of values...

Bob Phillips
07-06-2005, 05:39 AM
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
07-06-2005, 05:47 AM
Thanks....anyway.

Sir Babydum GBE
07-06-2005, 08:44 AM
:hi: 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
07-06-2005, 09:01 AM
Thats right... thanks

Bob Phillips
07-06-2005, 09:54 AM
:hi: 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