WorksheetFunction.CountIf

Hi guys,

I'm new to VBA but slowly learning, I'm not sure if what I am trying to do is possible in the way I'm trying to do it. Any help/guidance would be much appreciated.

I am trying to set the value of a range of cells to be the count of a range of cells in another range. To try and keep it simple I am using this code:

Range("J1:J5").Value = WorksheetFunction.CountIf(Range("A1:A20"), Range("I1:I5"))

I get a mismatch error, as I said any help would be great.

Thanks in advance.

Thanks for the reply KOKOSEK, I probably should have been clearer with my question. I have a list of lets say 20 numbers in column A, and a list of 5 numbers in Column I, in Column J I want the macro to show how many times each number appears in column A. A separate value for each number, and I am hoping to do this with one line of code.

Thanks again

Hi !

Could be done with a single codeline if only a sample workbook was attached …

Feel a little stupid but can't actually see how to attach, when I click on the Attachments button it just shows a small dropdown box with nothing in. Doesn't seem to be an option to actually attach anything.....I presume I'm missing something?

As you can see:


Capture.JPG

Do not feel stupid as a new member with few posts you may not be yet authorized to attach any file …

Wait a little until I have time to give it a try.

Thanks Marc, to be honest I have simplified what I am trying to do just so it's easier to explain. What I actually want to do is have a list of product numbers in Column A (in the spreadsheet I'm testing with at the moment its approx 700 numbers), with numbers in row 2 from Columns E:AF. I want a CountIfs to count the amount of times product appears in a list on a separate tab where in the number in Row 2 also appears in another Column. Much easier to explain if I could attach obviously.

Capture.JPG

Thanks Marc, just trying to get my head around why it works......the IF({1}......is stumping me?

Without the IF the VBA Evaluate method - a must read within VBA inner help ! - returns wrong results
(change your data with four 3 for example) …

So the IF forces Evaluate method like "Hey, it's an array formula to apply here !" …

Thanks very much Marc. Think it's now allowing me to attach things finally. So I am trying to adapt the solution you gave to fir my exact need, the workbook I've uploaded, in the Lines tab, I want the code to write the values in from B3:AC? - where ? is the amount of products in Column A.
It needs to count the amount of times the product code appears in the Data with the corresponding RC in Column B from the Row 2 of the Lines tab....if that makes sense.

As I can't see any link with your initial post, clarify with a sample and its expected result …

Is Inspection from Lines!B:C columns the same like R/C ?

So if you were to think of it as a formula in Lines!B3 would be =COUNTIFS(Data!$A:$A,Lines!$A3,Data!$B:$B,Lines!B$2)
Formula is to be expanded to all the rows with Products in and all the Columns up to AC

Yes it's so obvious now !

I have to change computer 'cause my actual version is too old (as COUNTIFS does not exist in 2003 version)
and to test an alternative way which may be faster, so see you later …

A demonstration as a beginner starter according to your formula & attachment :

PHP Code: 

Sub Demo1()
     Dim F$
    With Sheet3.UsedRange.Columns
         F = "=COUNTIFS(" & .Item(1).Address(External:=True) & ",$A3," & .Item(2).Address(External:=True) & ",B$2)"
    End With
    With Sheet5.UsedRange
        With .Range("B3").Resize(.Rows.Count - 2, .Columns.Count - 1)
             .Formula = F
             .Formula = .Value2
        End With
    End With
End Sub 


Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

So the faster way :

PHP Code: 

Sub Demo2()
            Dim VA, VH, V(), R&, C%, K$
    With Sheet3.UsedRange.Columns
        VA = .Parent.Evaluate(.Item(1).Address & "&""#""&" & .Item(2).Address)
    End With
    With CreateObject("Scripting.Dictionary")
        For Each VH In VA:  .Item(VH) = .Item(VH) + 1:  Next
    With Sheet5.UsedRange
        VA = .Range("A3", .Cells(.Rows.Count, 1)).Value2
        VH = .Range("B2", .Cells(2, .Columns.Count)).Value2
    End With
            ReDim V(1 To UBound(VA), 1 To UBound(VH, 2))
        For R = 1 To UBound(VA)
            For C = 1 To UBound(VH, 2)
                K = VA(R, 1) & "#" & VH(1, C)
                If .Exists(K) Then V(R, C) = .Item(K)
            Next
        Next
            .RemoveAll
    End With
        Sheet5.[B3].Resize(UBound(V), UBound(V, 2)).Value2 = V
End Sub 


Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

Thanks very much Marc.