1. ## Exclude duplicates master spreadsheet

Trying to help a user exclude duplicate entries from several sheets using a master sheet with totals. Each individual sheet calculates correctly using the following formula, =SUMPRODUCT((D721<>"")/COUNTIF(D721,D721&"")). How do I use a formula like this to total from 10 worksheets? The following formula from the total page does not work as it just sums D5 from each of the schools. =SUM('North Schuykill'!D5,Shamokin!D5,Shikellamy!D5,Pottsville !D5,'Mount Carmel'!D5,Mahanoy!D5,Berwick!D5,Milton!D5)

My apologies for any confusion as I am out of my comfort zone with this problem.
Thanks

I have attached spreadsheet, thank you for any assistance.

2. The system converted something you typed to a smiley face (probably a colon and D21).

If you're trying to sum from D7 to D21 on all of the sheets, and the sheets are contiguous, try this:

=sum('North Schuykill:Milton'!D7 : D21)

[no spaces around the colon]

3. Hey Y'all,

To prevent the smiley faces from use of the colon enclose the formulas in NOPARSE tags {noparse} formula here {/noparse}
Just REPLACE the {} with [] for it to work properly.

4. Hi RG

Is there a code for strikethrough?

zeddy

5. Zeddy,

I can't find one. You can find a full list of codes HERE. HTH

6. RG: Is there a list of these codes somewhere on the site?

Oops...overlap...thanks!

7. Thank you the timesaving tip. Much appreciated. How can I incorporate the countif function into the sum for all schools? Each school district has a sheet and within the sheet there are students names and the schools they applied to. If 10 students from North Schuykill each applied to the same university, we only want to count this university one time. Countif works on the school district sheets however the total sums each sheet and therefore each university is being counted several times as students from any of the high schools could potentially apply to the same university. We only want to count each university one time on the total page.

Thank you

8. Spreadsheet uploaded to initial post. Would greatly appreciate any assistance. Thank you so much for your expertise.

9. mlynnmills,

The following code will calculate the number of unique colleges throughout the workbook that the students applied to. The number will be updated on the Totals sheet when the workbook opens and each time the Totals sheet is activated. There is a hidden sheet called "Settings" and must remain as the first sheet.

HTH,
Maud

Place in a standard module:
Code:
```Public Sub UniqueColleges()
Application.ScreenUpdating = False
'--------------------------------
'DECLARE AND SET VARIABLES
Dim ws1 As Worksheet
Set ws1 = Worksheets("Settings")
ws1.Cells.ClearContents
Row = 1
'--------------------------------
'LIST ALL COLLEGES ON HIDDEN SHEET
For I = 3 To Worksheets.Count
With Worksheets(I)
LastRow = .Cells(Rows.Count, 4).End(xlUp).Row
For J = 7 To LastRow
ws1.Cells(Row, 1) = .Cells(J, 4)
Row = Row + 1
Next J
End With
Next I
'--------------------------------
'FIND UNIQUE COLLEGES AND RETURN NUMBER
LastRow = ws1.Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Totals").Range("C6") = WorksheetFunction.CountA(ws1.Range("A1:A" & LastRow))
Application.ScreenUpdating = True
End Sub```

10. Your expertise is very much appreciated! I will open and forward to admissions area for testing. Thank you so much.

11. UDF version:

Code:
```Public Function UniqueColleges(sColumn As String) As Long
Dim ws                    As Worksheet
Dim oDic                  As Object
Dim LastRow               As Long
Dim n                     As Long
Dim vData
Application.Volatile True

Set oDic = CreateObject("Scripting.Dictionary")

For Each ws In ThisWorkbook.Worksheets
If Not ws Is Application.Caller.Worksheet Then

LastRow = Application.Max(ws.Cells(Rows.Count, sColumn).End(xlUp).Row, 7)

vData = ws.Range(ws.Cells(7, sColumn), ws.Cells(LastRow, sColumn)).Value2
If IsArray(vData) Then
For n = 1 To UBound(vData, 1)
If Len(vData(n, 1)) <> 0 Then oDic(vData(n, 1)) = Empty
Next n
Else
If Len(vData) <> 0 Then oDic(vData) = Empty
End If
End If
Next ws
UniqueColleges = oDic.Count

End Function```
called using =UniqueColleges("D")

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•