Count the number of times values in Range B appear in Range A

[larryjfoster]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]Active Groups #
[/TD]
[TD]Group #
[/TD]
[TD]Region
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]000002
[/TD]
[TD]007895
[/TD]
[TD]West
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]008975
[/TD]
[TD]000002
[/TD]
[TD]West
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Hello, I can't seem to find a workable vba solution for this problem:I want to count the number of active groups per region.
i.e,
West Active Group Count =1 (Group00002 is the only active group)
The active groups column would actually be in another opened workbook
Any thoughts on how to get started would be greatly appreciated.

 

[ncrcnbl]

Second worksheet, A1: =Sheet1!$A1, copy down
Second worksheet, A2: =COUNTIF(Sheet1!$B:$B,Sheet2!$A2)

 

[ncrcnbl]

Apologies, I should have seen PER REGION

 

[ncrcnbl]

Second worksheet, A1: =Sheet1!$A1
then copy that down

Second worksheet, B1: ="East"
Second worksheet, C1: ="West"
Second worksheet, D1: = ... (whatever the other region names are, to the right, across row 1)

Second worksheet, B2: =COUNTIFS(Sheet1!$B:$B,Sheet2!$A2,Sheet1!$C:$C,B$1 )
then copy that down and to the right, filling in the matrix against the list of groups and across the list of regions

 

[larryjfoster]

Thank you ncrnbl for the quick reply; however, it is a VBA solution I am seeking. i.e.

For each cell In column B
If cell.value exists in column A Then count = count + 1
End If
Next

I'm pretty new to VBA so having trouble working out exactly what I need

 

[ncrcnbl]

I can take a bite out of this, and get to what i think is the "meat" of this problem,

Before you start I assume you should have a backup of all your work, not have anything else running you can't afford to have running, that you read the program to figure out what you expect it to do, that you know how to stop the program once it starts.

This skips some basic skills you'll need to build on.
-selecting or finding the list of active groups or regions, finding where the list ends
-naming or picking a specific workbook, do you understand the object model in Excel VBA? Workbook/Worksheet/Range, using those variables

Let's say Sheet two is your target, and Sheet one has your list of groups and regions in columns B and C like you have in your example.
In Sheet Two, by hand, make your sorted list of Active Groups, in order, down the left, from A2 down, and your regions across the right, from B1 across. The range within this table which would have your answers is assumed blank, empty.

In the example below, the addresses are hardcoded.
Take a look at this, use F8 to run the instructions one row at a time, hover over the variable names to verify it's doing what you expect, etc.

'=================
Public Sub matrixCountIfs()

    Dim rngTarget As Range
    'a matrix with column 1 headers on the left and column 2 headers on the right
    Dim rngSource As Range
    'two columns, we want to count where column 1 and column two match
    Dim y As Long, x As Long, r As Long, c As Long
    
    
    Set rngSource = ThisWorkbook.Sheets("Sheet1").Range("B2:C12")
    Set rngTarget = ThisWorkbook.Sheets("Sheet2").Range("A1:D7")
    
    For r = 2 To rngTarget.Rows.Count
        For c = 2 To rngTarget.Columns.Count
            'using these first two loops, we look at every possible cell in the target matrix
            For y = 1 To rngSource.Rows.Count
                'now we interate down the rows in the source, this looks at the whole list for every cell in the target matrix
                If UCase(rngSource.Cells(y, 1)) = UCase(rngTarget(r, 1)) And UCase(rngSource.Cells(y, 2)) = UCase(rngTarget(1, c)) Then
                    rngTarget.Cells(r, c).Value = rngTarget.Cells(r, c).Value + 1
                End If
            Next y
        Next c
    Next r
End Sub
'=================