How to Count cells in a dynamic range matching two criteria given in table headers

How to count cells in a dynamic range matching two criteria given in table headers

Requirement:

The user has a workbook with two sheets - say "core data" and "plot", which I get from an access database and has always different length (rows).

What the user does is basically copy column "N" of "core data" sheet, remove the duplicates and paste them in column "A" starting from cell "A2" of "Plot" sheet. The same I do with column "F" of "core data" sheet, but paste them in row 2, starting from cell "B2" of "Plot" sheet. A table I have now. The size of which depends on the database size.

What the user wants to do after this point is, for each cell in the table, to go through columns N and F of "Core Data" sheet and count cells that match both the value in A column and 1st raw of "Plot" sheet (which are the "headers?" of the table - The user has attached an image of how the worksheet "Plot" looks). The user can never know size of the table.

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/148514-count-cells-in-a-dynamic-range-matching-two-criteria-given-in-table-headers

Solution:

Have you considered a pivot table? Column N as the column field, column F as the row field, and ID (count of) as the data field. This will automatically remove duplicates.

Programatically,

Code:

Sub x()

Dim ws As Worksheet, pvtCache As PivotCache, pvt As PivotTable
Dim StartPvt As String, SrcData As String

SrcData = "RMA Core data!" & Sheets("RMA Core data").Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1)
Set ws = Sheets.Add
StartPvt = ws.Name & "!" & ws.Range("A3").Address(ReferenceStyle:=xlR1C1)
Set pvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SrcData)
Set pvt = pvtCache.CreatePivotTable(TableDestination:=StartPvt, TableName:="PivotTable1")

With pvt.PivotFields("Source")
    .Orientation = xlRowField
    .Position = 1
End With

With pvt.PivotFields("Error code")
    .Orientation = xlColumnField
    .Position = 1
End With

pvt.AddDataField ActiveSheet.PivotTables(pvt.Name).PivotFields("ID "), "Count of ID ", xlCount

End Sub

Obtained from the OzGrid Help Forum.

Solution provided by StephenR.