We help IT Professionals succeed at work.

# VBA - Countif from another sheet

on
Hello,

I have this sheet 1 that i have multiple duplicated names in column Q.

In my Sheet2 column A starting at row 11, i have a single list of those names.

I need to put the number of time each names are showing in sheet 1 column Q.

How can i do that.

Thanks again for helping.

Sheet1

Sheet2
Comment
Watch Question

## View Solution Only

Commented:
enter the following in cell B11 in sheet two. Note you will have to change the names of the sheets in this formula to the names of the sheets in your workbook.
``````=COUNTIF('SHEET 2 NAME'!Q:Q,'SHEET 1 NAME'!A11)
``````

alternatively you can also use
``````=COUNTIF('SHEET 2 NAME'!Q:Q,A11)
``````

Commented:
Is it possible to do this with a Macro? I'm already using the formula in the example i gave.
Analyst Assistant

Commented:
Use a pivot table?

Commented:
Sorry Norie but for this analysis file, i cannot use the pivot table option. But you are right, that could of been a very good option.

Commented:
In my project, the name may be changing and also, i may have up to 65000 records of names in column Q from Sheet 1
Top Expert 2015

Commented:
Wilder,

You can use the following code to do what you are looking for..

``````Sub counters()

Dim rng As Range, cell As Range
Dim r As Range
Dim lrow As Long, lr As Long
Dim ws As Worksheet, ws1 As Worksheet

Set ws = Sheets("Sheet1")
Set ws1 = Sheets("Sheet2")

lr = ws.Cells(Cells.Rows.Count, "Q").End(xlUp).Row
lrow = ws1.Cells(Cells.Rows.Count, "A").End(xlUp).Row

Set rng = ws.Range("Q2:Q" & lr)
Set r = ws1.Range("A11:A" & lrow)

For Each cell In r
If Trim(cell.Value) <> "" Then cell.Offset(0, 1).Value = Application.WorksheetFunction.CountIf(rng, cell.Value)

Next cell

End Sub
``````

Saurabh...
Analyst Assistant

Commented:
Why can't you use a pivot table?

Is it the changing amount of data?

Commented:
No, The excel file structure is changing from time to time.

I'm almost with the final result.

So far, i have:
``````Private Sub CommandButton2_Click()
Dim rng As Range, cell As Range
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")

lr = ws1.Cells.Find(what:="*", searchorder:=xlRows, searchdirection:=xlPrevious).Row
Set rng = ws1.Range("Q2:Q" & lr)

For Each cell In rng
ws2.Range("B" & cell.Row) = Application.WorksheetFunction.CountIf(Sheets("Sheet1").Range("Q2:Q1000"), ws2.Range("A" & cell.Row))
Next cell
End Sub
``````
Top Expert 2015

Commented:
Alternatively you can use the following code as well..If you don't want to apply loop...

``````Sub counters()

Dim rng As Range, cell As Range
Dim r As Range
Dim lrow As Long, lr As Long
Dim ws As Worksheet, ws1 As Worksheet

Set ws = Sheets("Sheet1")
Set ws1 = Sheets("Sheet2")

lr = ws.Cells(Cells.Rows.Count, "Q").End(xlUp).Row
lrow = ws1.Cells(Cells.Rows.Count, "A").End(xlUp).Row

Set rng = ws.Range("Q2:Q" & lr)

ws1.Range("B11:B" & lrow).Formula = "=COUNTIF(" & ws.Name & "!" & rng.Address & ",A11)"
ws1.Range("B11:B" & lrow).Value = ws1.Range("B11:B" & lrow).Value

End Sub
``````
Data Analyst

Commented:
You can try this code:
``````Sub ee_CountIf()
Dim rng As Range, rng2 As Range, l As Long, ws As Worksheet, ws2 As Worksheet

Set ws = ActiveWorkbook.Sheets("Sheet1")
l = ws.Cells(Rows.Count, "Q").End(xlUp).Row
Set rng = ws.Range("Q2:Q" & l)

Set ws2 = ActiveWorkbook.Sheets("Sheet2")
l = ws2.Cells(Rows.Count, "D").End(xlUp).Row
Set rng2 = ws2.Range("D11:D" & l)

For Each cell In rng2
ws2.Cells(Rows.Count, "E").End(xlUp).Offset(1, 0).Value = Application.WorksheetFunction.CountIf(rng, cell.Value)
Next cell
End Sub
``````

Commented:
pretty quick, both looks good. let me test them both and i'll be back shortly.
Top Expert 2015

Commented:
Wilder1626,

Basis of my experience in large data sets where i have applied macro's... The second one will be slow..Comparatively the first one...since in the second one i'm triggering excel calculations of all the range in 1 go and excel at times go crazy or takes good amount of time depending upon your computer configuration and other things as well when you do that..

In first macro i take each cell at a time..and then apply  a formula so i don't trigger all the calculations in 1 go..

Saurabh...

Commented:
I get what you are saying. Make sense!

I just found out that i'm missing 1 small detail on my end, that i should of talked about at the beginning.
I need to also take column K from sheet 1 + Column Q.

Ex:
Column K                     Column Q
F2345                             CARL
F2345                             CARL

Since column K and Q are the same, it should count only 1 for Carl.
Top Expert 2015
Commented:
You can use the following code..What it will do it will check unique values basis of Column-K and Column-Q and will give you a count of only unqiue values....

``````Sub counters()

Dim rng As Range, cell As Range
Dim r As Range
Dim lrow As Long, lr As Long
Dim ws As Worksheet, ws1 As Worksheet

Set ws = Sheets("Sheet1")
Set ws1 = Sheets("Sheet2")

lr = ws.Cells(Cells.Rows.Count, "Q").End(xlUp).Row
lrow = ws1.Cells(Cells.Rows.Count, "A").End(xlUp).Row

Set rng = ws.Range("Q2:Q" & lr)
Set r = ws1.Range("A11:A" & lrow)

For Each cell In r
If Trim(cell.Value) <> "" Then cell.Offset(0, 1).Value = countunqiue(rng, -6, cell.Value)

Next cell

End Sub

Function countunqiue(r2 As Range, k As Long, st As String)
Dim c As Range
Dim col As New Collection

On Error Resume Next
For Each c In r2

If UCase(Trim(c.Value)) = UCase(Trim(st)) Then

End If

Next c

countunqiue = col.Count

End Function
``````

Commented:
Hi everyone

I did the test last night with about 124000 records in the sheet and i must say that the execution time was very impressive with Saurabh Singh Teotia.  Since i will always have huge files to work with, i must go with that solution.

Thanks again to all for your time.