Excel VBA: Count distinct values in a column, on a different worksheet, if search term on worksheet A matches search term on worksheet B

[lostwhisper]

I have a workbook containing multiple worksheets:

(a) There is a worksheet that lists a series of filenames (called "Summary"). This worksheet also includes a column for each month of the year (JAN, FEB, MAR etc.) to record the number of times the filename has been accessed that month.

(b) There is a worksheet for each month of the year (JAN, FEB, MAR etc.). These worksheets contain a data dump of how many times the filenames in worksheet (a) have been accessed that month. They include columns Filename, Username, and Date Accessed. A new row is created each time a filename is accessed.

Objective: I want to count the number of times the filename on worksheet (a) appears on worksheet (b). So if FilenameX has been accessed 20 times in January, I want to return a count of 20 for that month.

I also want the formula/custom function that calculates the number of times the filename on worksheet (a) appears on worksheet (b), to be generic, so that it can calculate for all calendar months without having to be updated.

Problem: If Joe Bloggs and his sister, Jenny Bloggs, both access FilenameX on the same two days of the month, i.e. January 1st and January 31st, four records appear on worksheet b:

- Joe Bloggs, FilenameX, 1st January 2017
- Jenny Bloggs, FilenameX, 1st January 2017
- Joe Bloggs, FilenameX, 31st January 2017
- Jenny Bloggs, FilenameX, 31st January 2017

However I only want to count the unique days on which FilenameX was accessed that month. So in this instance that would be 1st January 2017 and 31st January 2017, which is 2.

Attempted solution: I can use a COUNTIF to correctly return a count of the number of times FilenameX on worksheet (a) appears on worksheet (b). It's a generic formula which will calculate for all months without having to be updated, however it DOESN'T handle duplicates:

=COUNTIF(OFFSET(INDIRECT("'"&I$1&"'!$A$1"),0,0,COUNTA(INDIRECT("'"&I$1&"'!$D:$D")),1),'Summary'!$F5)

Attempted update (to the attempted solution): In my attempt to count unique date values only, I've tried to adapt the formula above by incorporating an example formula I found on exceljet.com:

=SUMPRODUCT(1/COUNTIF(data,data&""))

However, rather unfortunately this crashes Excel!

Lightbulb moment: What I want to do will likely require a VBA custom function, and so after a search I came across the following code on stackoverflow.com:

Option Explicit

Public Function CountDistinct(r As Range) As Long

  Dim col As Collection
  Dim arr As Variant
  Dim x As Long
  Dim y As Long

  Set col = New Collection

  arr = r

  On Error Resume Next

    For x = 1 To UBound(arr, 1)
      For y = 1 To UBound(arr, 2)
        col.Add 0, CStr(arr(x, y))
      Next
    Next

  On Error GoTo 0

  CountDistinct = col.Count

  Set col = Nothing
  Set arr = Nothing
  Set r = Nothing
End Function

This function works well, and will correctly count the distinct values in any range that I pass to it. For example if I want to count the unique values in Date Accessed (column D) on worksheet JAN, I can call the function as follows:

=CountDistinct(INDIRECT("'"&I$1&"'!$D$1:$D$55"))

And it will return the correct value (yay!).

However (part 1 of 2)... In order for it to work, I need to hardcode the range that I pass to the function, in this example D1:D55. Instead, I need the range to increase or decrease dynamically (i.e. $D:$D in the Attempted Solution above).

However (part 2 of 2)... I also want to return a count where the Filename on worksheet (a) matches a Filename on worksheet (b), otherwise I would like to indicate a "Not Found" or "N/A" notification in the cell on worksheet (a). I'm not sure how to call the function and execute a count only if a matching Filename is found on worksheet (b).

Request for help & advice: Please can someone help steer me in the right direction? Any and all guidance is greatly appreciated! Apologies for the long post btw!

My sincerest thank you in advance!