I'm not sure where to start

[TinaG]

I've got an Excel workbook with many worksheets in it. Each worksheet has
the same three columns and one column (Name) has many duplicates across the
different worksheets (but no dupes on the same one). Each worksheet has a
'Number Of' column that shows the number of that 'Name' that is owned. I'd
like to create a way to get a total 'Number Of' each 'Name' that is on each
worksheet. Pretty much if 'Name' is duplicated and owned I want a total
number of owned.

Name Rarity $ #Of
Aladdin's Lamp R 0.25
Aladdin's Ring R 0.25 2
Animate Wall R 0.25
Ankh of Mishra R 0.56

 

[Shane Devenshire]

Hi,

It looks like you will need code.

Sub CountDups()
Dim cel As Range
Dim cell As Range
Dim sh As Worksheet
Dim myCount As Integer

For Each cel In Sheets("Summary").Range("A2:A5")
If cel = "" Then Exit Sub
For Each sh In Worksheets
If sh.Name <> "Summary" Then
For Each cell In sh.Range("A2:A" &
sh.Range("A65536").End(xlUp).Row)
If cell = cel Then
myCount = myCount + 1
End If
Next cell
End If
Next sh
If myCount > 1 Then
cel.Offset(0, 3) = myCount
End If
myCount = 0
Next cel
End Sub


To add this code to a workbook press Alt+F11 and select your file in the
Project explorer in the top left side of the screen. Choose Insert, Module.
Put the code in the resulting module.

You will need to modify the code by changing the summary sheet name and the
A2:A5 address.

 

[Herbert Seidenberg]

Excel 2007 Pivot Table
No code, no formulas.
Aesthetically pleasing.
http://www.mediafire.com/file/t2k31dqmvie/03_22_09.xlsx

 

[Ashish Mathur]

Hi,

You essentially want to do a SUMIF() across multiple sheets. You may try
this formula where L3:L5 holds the tab names of the individual sheets, A2
holds the name and D48 holds column to be summed

=SUMPRODUCT(SUMIF(INDIRECT(L3:L5&"!D48"),A2))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com