Count with multiple variants

Question

I have used Variants in the past for something similar, but it was one dimensional in its solution. I am wondering if utilizing Variants with two dimensions would be feasible.

I have ever changing list of dates that correspond with a week, that will be entered in chronologically. The next column is the count of that week. Column C is the building that it took place in.

For example, the first row shown in the image above takes place the week of "10/2/2016" and there was a count of 8 that week which means the buildings in rows 2-9 are correlated with that week, and it continues on for each corresponding week.

I have the sum of the counts for each year, so for the chart in "E1:G14", I want to count each time the building is counted for each year, respectively. I am just confused as to how to approach it and if Variants would be useful or if using a CountIfs for the ranges would work best.

Thank you in advance.

CODE

Private Sub maybe()

    Dim sht As Worksheet: Set sht = Worksheets("Sheet3")
    Dim wk_cnt As Double: wk_cnt = sht.Range("A1", sht.Range("A1").End(xlDown)).Rows.Count
    Dim bld_cnt As Double: bld_cnt = sht.Range("C2", sht.Range("C2").End(xlDown)).Rows.Count
    Dim cnt As Double
    Dim yrs_cnt As Double
        If sht.Range("D3").Value = "" Then
            yrs_cnt = 1
        Else:
            yrs_cnt = sht.Range("D2", sht.Range("D2").End(xlDown)).Rows.Count
        End If
    Dim yrsArray As Range
        If sht.Range("D3").Value = "" Then
            Set yrsArray = sht.Range("D2")
        Else:
            Set yrsArray = sht.Range("D2", sht.Range("D2").End(xlDown))
        End If
    Dim vCnts As Variant

    ReDim vCnts(1 To 12, 1 To yr_cnt)
        vCnts(1, 1) = "Irving Building"
        vCnts(2, 1) = "Memorial Building"
        vCnts(3, 1) = "West Tower"
        vCnts(4, 1) = "Witting Surgical Center"
        vCnts(5, 1) = "Madison Irving Surgery Center"
        vCnts(6, 1) = "Marley Education Center"
        vCnts(7, 1) = "410 South Crouse"
        vCnts(8, 1) = "Physicians Office Building"
        vCnts(9, 1) = "Crouse Business Center"
        vCnts(10, 1) = "Commonwealth Place"
        vCnts(11, 1) = "Crouse Garage"
        vCnts(12, 1) = "CNY Medical Center"

    For x = 1 To yrs_cnt
        cnt = 0
        For y = 2 To wk_cnt
            If Year(sht.Cells(y, 1).Value) = sht.Cells(1, x + 5).Value Then
                cnt = cnt + sht.Cells(y, 2).Value
                sht.Cells(14, x + 5) = cnt
            End If
        Next y
    Next x

End Sub

EDIT

With Column C

With only Columns A & B

I need the numbers to match the second image, but when I include all three columns it looks like the first image after I group it by years year. How can I fix that?

@SJR - How would I go about doing that for this scenario. With different length columns? — A Cohen, Mar 19 '18 at 15:10

score 1 · Accepted Answer · answered Mar 19 '18 at 17:17

It looks like your PivotTable is using the "Count" column improperly. Where it says "Count of Count", it's telling you that the number shown is how many lines on your data range fit the selected criteria. I think if you change the Value Field Settings to SUM you will be pleased with the difference. See below:

Count with multiple variants

1 Answers1