Count if, display results in msgbox

Originally Posted by j9070749

Hi all, I am unsure if this will be easier if I just used a formula and displayed the results in a cell.

I wouldn't mind helping, but can you supply a sample workbook and at least for me, it may be easier to display in a cell versus a message box.

On second thought, have you considered a pivot table?

Maybe you could upload an example.

see if the attached is something you can work with?

it does not use macros, and the "loop" list will grow as you add more

edit: revised by adding a pivot table as a 2nd alternative

See how this works for you and a big thanks to Moo for giving me a helping hand (and a good set of eyes).

Another method for building this type of table is to use the Dictionary Object, e.g:

Public Sub Example()
    Dim oDic As Object
    Dim rngCell As Range
    Dim vStr As Variant, vKey As Variant
    Dim lngKey As Long
    Set oDic = CreateObject("Scripting.Dictionary")
    With Sheets("Sheet1")
        .Columns("O:P").Clear
        For Each rngCell In .Range(.Cells(2, "D"), .Cells(.Rows.Count, "D").End(xlUp))
            With oDic
                .CompareMode = vbTextCompare
                v = rngCell.Value & "-" & rngCell.Offset(, 9).Value
                If Not .exists(v) Then
                    .Add v, 1
                Else
                    .Item(v) = .Item(v) + 1
                End If
            End With
        Next rngCell
        With oDic
            ReDim vKeys(1 To .Count, 1 To 2)
            For Each vKey In .Keys
                lngKey = lngKey + 1
                vKeys(lngKey, 1) = vKey
                vKeys(lngKey, 2) = .Item(vKey)
            Next vKey
        End With
        Set oDic = Nothing
        .Cells(1, "O").Resize(, 2).Value = Array("Device", "MyCount")
        .Cells(2, "O").Resize(UBound(vKeys, 1), 2).Value = vKeys
    End With
End Sub

You could iterate the Dictionary and write straight back to Sheet rather than use Array, however, less to / fro Worksheet / VBE using the Array (ie single update).

My take on Jeffrey's "use a formula" method, I like it because it's loopless and uses the autofilter:

Option Explicit

Sub CountTable()
Dim LR As Long

Application.ScreenUpdating = False
With ActiveSheet
    LR = .Range("D" & .Rows.Count).End(xlUp).Row
    .Range("N1") = "Devices"
    .Range("N2:N" & LR).FormulaR1C1 = "=RC4&""-""&RC13"
    On Error Resume Next
    ActiveWorkbook.Names("Extract").Delete
    
    .Range("N:N").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("Q1"), Unique:=True
    .Range("R1") = "MyCount"
    
    LR = .Range("Q" & .Rows.Count).End(xlUp).Row
    With .Range("R2:R" & LR)
        .FormulaR1C1 = "=COUNTIF(C14, RC17)"
        .Value = .Value
    End With
    .Range("N:N").ClearContents
    .Range("Q:R").Columns.AutoFit
End With
Application.ScreenUpdating = True
End Sub

Well Merry Christmas to me and the OP and thank you so much for the assist. I woke up this morning not knowing what to expect and as usual you guys came through with sharing your experience/knowledge.

@Luke, I've studied the scripting dictionary and love it, but at the same time, always confused by it. I've stepped through your sample quite a few times and it is sinking in ever so slowly. When first running the example it seems a variable was missed in the declaration, v, so I added it as a variant. Is this the correct declaration?

@Jerry, I initially looked into the advanced filter, but when two columns where involved I scraped the idea, but now seeing how you used the concatenate and THEN the advanced filter, I should have thought longer and harder. It seems you may have left a piece of testing code behind in the form of...

ActiveWorkbook.Names("Extract").Delete

Thanks again to all. I have learned so much from you and hope to continue...

God Bless and Merry Christmas to all...

Originally Posted by jeffreybrown

@Luke, I've studied the scripting dictionary and love it, but at the same time, always confused by it. I've stepped through your sample quite a few times and it is sinking in ever so slowly. When first running the example it seems a variable was missed in the declaration, v, so I added it as a variant. Is this the correct declaration?

Error on my part - I modified my original (stored .bas) but failed to modify the Variant from vStr to v so yes the above is correct. Apologies for oversight but good to know people are running Option Explicit

You are most welcome. We are all glad to have helped and happy you have a solution to work with.