VBA Help!!! - Loop/Count/Multiple criterias - Summarise matrix of data

[damonkey]

I need help with a VBA code that I am having trouble with at the moment. I am working two worksheets from the one workbook.

Information on sheet 1 (TEST) - detailed worksheet

1. In column A, starting at row 9, there are a list of companies (Company A, Company B, Company C etc...). There are duplicate entries.
2. In row 7, starting at column 9, there are month-year information (i.e. 072016, 082016, 092016). Similarly, there are duplicate entries.
3. Now, when you link something in column A with the corresponding date field (i.e. [Company A:072016],[Company B:082016] etc...), there could be some text written (i.e. "meeting with person") in the corresponding cell or it could be blank (i.e. nothing). This goes on.. exactly like a matrix of information for any combination of companies and date fields.

Information on sheet 2 (Summary) - summary worksheet

1. In column A, starting at row 2, there is a unique list of companies (Company A, Company B etc...) (i.e. unique list created from sheet 1 but without duplicates)
2. In row 1, starting at column 2, there is a unique list of month-year information (i.e. 072016, 082016 etc...) (i.e. unique list created from sheet 1 but without duplicates.

Problem

1. In sheet two (with the unique list of companies and date criteria) I want to count the number of instances where the corresponding cell (as explained in point 3 - sheet 1, above) is "not blank" based on multiple criterias - the select company (criteria 1) and the select date field (criteria 2)
   • For example, in Sheet 2, column B row 2 (which is blank) - I want to populate this with a count of cells in sheet 1 that is not blank and fits into the criteria of the selected company and date field.

Not sure if the above makes sense. But hopefully the code that I have below will be able to tell the story that I am trying tell.

Sub Summary()


Dim lastcolumn2 As Long
Dim lastcolumn1 As Long
Dim lastrow2 As Long
Dim lastrow1 As Long
Dim ws2 As Worksheet
Dim ws1 As Worksheet
Dim counta As Integer


Set ws2 = Worksheets("TEST")
Set ws1 = Worksheets("Summary")


counta = 0


lastcolumn2 = ws2.Range("XFD6").End(xlToLeft).Column
lastrow2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row
lastcolumn1 = ws1.Range("XFD1").End(xlToLeft).Column
lastrow1 = ws1.Cells(Rows.Count, "A").End(xlUp).Row


For i = 9 To lastrow2
' ' rows of summary sheet

For k = 2 To lastrow1
' rows of test sheet

If ws2.Cells(i, 1).Value = ws1.Cells(k, 1).Value Then 'matched company

For j = 9 To lastcolumn2
'columns of summary sheet

For l = 2 To lastcolumn1
' columns of test sheet

If ws2.Cells(6, j).Value = ws1.Cells(1, l).Value Then

If ws2.Cells(i, j).Value <> "" Then

counta = counta + 1

End If

End If

Next l

Next j

End If

Next k

Next i

End Sub

If someone could help me this please, that would be super awesome - I have just spent ages thinking about this. I know i need to reset the count somewhere in the code for each "company" that I look for and then a way for me to "publish" the results accordingly to sheet 2.