WorksheetFunction.CountIf with a range of values as criteria

[MSalvador]

Hi!

I've been dealing with a problem which I can not find information about it. So what I want to make is a count of records depending on a criteria, I know I can accomplish it with WorksheetFunction.CountIf but let me explain a bit further. The problem I face is giving the criteria argument as a range like as a matrix formula (I believe that is the thing) as in the next image:

But for WorksheetFunction.CountIf it appear does not work the same way as in a formula in a cell giving me a Type Mismatch error. I make it work with WorksheetFunction.XLookup, being my code:

Dim wb As Workbook
Dim ws As Worksheets, wsTable As Worksheets
Dim tableInfo As ListObject
Dim columnCompanyNameInfo As ListColumn, columnCompanyStateInfo As ListColumn
Dim lastRowWS As Integer

lastRowWS = ws.UsedRange.Rows.Count
    
Set wb = ThisWorkbook
Set ws = wb.Worksheets("Sheet1")
Set wsTable = wb.Worksheets("Table")
Set tableInfo = wsTable.ListObjects("Table1")
Set columnCompanyNameInfo = tableInfo.ListColumns("Company_Name")
Set columnCompanyStateInfo = tableInfo.ListColumns("Company_State")

ws.Range("G2", "G" & lastRowWS).Value = WorksheetFunction.XLookup(ws.Range("B2", ws.Range("B2").End(xlDown)).Value, columnCompanyNameInfo.DataBodyRange, columnCompanyStateInfo.DataBodyRange, 1)

For visual reference:

Is there a way I can replicate what I did with the XLookup in the CountIf function? Or the For loop it's my only solution? Below is the code I am trying but failed to get it work.

Dim wb As Workbook
Dim ws As Worksheet
Dim lastRowWS As Integer

Dim table As ListObject
Dim columnConcat As ListColumn

Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet1")

Set table = ws.ListObjects("Table1")
Set columnConcat = table.ListColumns("ConcatID_CompanyName")

lastRowWS = ws.UsedRange.Rows.Count

ws.Range("A2", "A" & lastRowWS).Value = WorksheetFunction.CountIf(columnConcat.DataBodyRange, ws.Range("A2", "A" & lastRowWS).Value &"-"&ws.Range("B2", "B" & lastRowWS).Value)

For visual reference:

Thanks for your responses!

 

[Peter_SSs]

Welcome to the MrExcel board!

Why are you trying to do the calculations with vba rather than just putting formulas in the sheet like you have shown?

If you really want/need to use vba to get the values, then could you use an idea like this? I have not used ListObjects, different worksheets etc like you seem to have as i don't have the details, but starting with the data in column C, the code below has produced the values in columns F:G

MSalvador.xlsm
	C	D	E	F	G
1
2
3
4	Column3			Column3	4
5	Column5			Column5	2
6	Column3			Column2	7
7	Column2			Column4	5
8	Column4			Column1	3
9	Column1
10	Column3
11	Column5
12	Column4
13	Column4
14	Column2
15	Column2
16	Column1
17	Column2
18	Column4
19	Column1
20	Column2
21	Column3
22	Column2
23	Column4
24	Column2
25
Sheet1

Sub Test_Countif()
  With Range("F4")
    .Formula2 = "=LET(r,C4:C" & Range("C" & Rows.Count).End(xlUp).Row & ",u,UNIQUE(r),HSTACK(u,COUNTIF(r,u)))"
    .SpillingToRange.Value = .SpillingToRange.Value
  End With
End Sub

 

[MSalvador]

Welcome to the MrExcel board!

Why are you trying to do the calculations with vba rather than just putting formulas in the sheet like you have shown?

If you really want/need to use vba to get the values, then could you use an idea like this? I have not used ListObjects, different worksheets etc like you seem to have as i don't have the details, but starting with the data in column C, the code below has produced the values in columns F:G

MSalvador.xlsm
	C	D	E	F	G
1
2
3
4	Column3			Column3	4
5	Column5			Column5	2
6	Column3			Column2	7
7	Column2			Column4	5
8	Column4			Column1	3
9	Column1
10	Column3
11	Column5
12	Column4
13	Column4
14	Column2
15	Column2
16	Column1
17	Column2
18	Column4
19	Column1
20	Column2
21	Column3
22	Column2
23	Column4
24	Column2
25
Sheet1

Sub Test_Countif()
  With Range("F4")
    .Formula2 = "=LET(r,C4:C" & Range("C" & Rows.Count).End(xlUp).Row & ",u,UNIQUE(r),HSTACK(u,COUNTIF(r,u)))"
    .SpillingToRange.Value = .SpillingToRange.Value
  End With
End Sub

Hi Peter! Thanks for the welcoming!

This step its part of a data cleaning process I am working on, that is why I am doing it in vba, also I thought making it work with the built-in countif for vba would be more efficient than trying to create a workaround solution. As for the different objects I use, the main purpose for writting them down was merly for if anyone saw something strange about the use of these elements that could be the reason of the error I get.

I have not found anything related to why I can not replicate what I did with the XLookup formula, but this solution does the trick, thanks!

I will still be looking the cause for the error I get on the CounIf vba formula hoping I can get an answer either is possible or not, just for the knowledge.

 

[Peter_SSs]

but this solution does the trick, thanks!

You are welcome. Glad to contribute.

 

[RoryA]

Is there a way I can replicate what I did with the XLookup in the CountIf function?

Use Application.Countif instead of Worksheetfunction.countif

 

[Alex Blakenburg]

I will still be looking the cause for the error I get on the CounIf vba formula hoping I can get an answer either is possible or not, just for the knowledge.

Your code and your image in Post #1 for the Countifs don't line up at all. This is based on your image.

Sub fncCountIf()

    Dim rng As Range, rCell As Range
    Dim arr1, arr2
    Dim lastRowWS As Long
    Dim i As Long
   
    lastRowWS = 8
   
    Set rng = Range("D4:D24")
   
    arr1 = Range("G4:G" & lastRowWS).Value
    arr2 = Range("H4:H" & lastRowWS).Value
   
    For i = 1 To UBound(arr1)
        arr1(i, 1) = arr1(i, 1) & arr2(i, 1)
    Next i
   
    Range("I4:I" & lastRowWS).Value = Application.CountIfs(rng, arr1)

End Sub

20240127 VBA Countifs MSalvador.xlsm
	A	B	C	D	E	F	G	H	I
1
2
3		ID	Name	Concat	Price		ID	Name	Price
4		ID 3	Column3	ID 3Column3	10		ID 1	Column1	1
5		ID 5	Column5	ID 5Column5	50		ID 2	Column2	2
6		ID 3	Column3	ID 3Column3	50		ID 3	Column3	3
7		ID 2	Column2	ID 2Column2	30		ID 4	Column4	3
8		ID 4	Column4	ID 4Column4	10		ID 5	Column5	2
9		ID 1	Column1	ID 1Column1	50
10		ID 3	Column3	ID 3Column3	20
11		ID 5	Column5	ID 5Column5	30
12		ID 4	Column4	ID 4Column4	40
13		ID 4	Column4	ID 4Column4	50
14		ID 2	Column2	ID 2Column2	10
Sheet1 (2)

Cell Formulas
Range	Formula
D4:D14	D4	=B4&C4