Count Unique values in multiple worksheets based on certain criteria

Hi guys

I really need your help in figuring out how to complete the below custom function in Excel.
The idea is simple . What I need is a function that will count unique values on certain worksheets in a defined column which will be the same for all these worksheets (this part is already solved with the below code) , but I want on top of this to report back the unique values given acertain criterion (this part is not solved yet).
Can you pls have a look at the below code and let me know what I am missing.
Thanks in advance for your support

ublic Function MultiSheetUniqueCount(Rng1 As Range, Rng2 As Range, Rng4 As Range, Criterion As Variant)
'Set up a reference to Microsoft Scripting Runtime in Tools > References
Dim Dict As Object
Dim Rng3 As Range
Dim Cell1 As Range
Dim Cell2 As Range
Application.Volatile
Set Dict = CreateObject("Scripting.Dictionary")
For Each Cell1 In Rng1
With Worksheets(Cell1.Value)
Set Rng3 = Intersect(.UsedRange, .Range(Rng2.Address))
If Not Rng3 Is Nothing Then
For Each Cell2 In Rng3
If Cell2.Value <> "" Then
If Dict.Exists(Cell2.Value) Then
'Do nothing
Else
Dict.Add Cell2.Value, Cell2.Value
End If
End If
Next Cell2
End If
End With
Next Cell1
MultiSheetUniqueCount = Dict.Count
End Function

Hi Mumps1

I do appreciate your fast response on this , I am attaching the relevant excel file for you to check.
As you can see the VBA formula in cell D2 and D3 in worksheet under the name "Master Table" it reads worksheets Lights_Utilisation & door Utilisation in column B:B and it returns the count of unique instances . Now what I want is for these unique instances to be also restricted on the values of column c:c of the "Master Table" , so when the formula works correctly the right numbers should be for Cyprus = 365 and for Greece = 5063 , now in both countries the formula returns the value of 5429.

Let me know if I have now explained in a more thorough manner
Thanks again for your generous offering to help me
Regards
Kyuss 21

The formula should lookup value C2 in the Master Table and return the number of 365 in cell D2 and lookup the value C3 in the Master Table and return the number of 5063 in cell D3 in the Master Table.

Hi Mumps1
If use the formula you are giving me I will automatically ignore also the unique values contained in worksheet Doors_Utilisation , that is why I have created this custom function in VBA under the name MultiSheetUniqueCount which reads the values both from Lights_Utilisation and Doors_Utilisation worksheets . Now what more I need is for this formula to be further constrained on the values of Master Table C2 , C3... etc.

Yes exactly that but add in your phrase the word "unique" i.e

I want to return the total unique counts for "Cyprus" and "Greece" from both sheets "Lights_Utilisation" and "Doors_Utilisation" in D2 and D3.

And of course this to be variable since I intend to populate the data with more countries in the future.

Thank you Mumps1
The formula you have given me COUNTIF(Lights_Utilisation!A$2:A$100000,C2)+COUNTIF(Doors_Utilisation!A$2:A$100000,C2) does not return back the unique values instead it reports back the count of all values regardless if they are unique or not. Dont spend time using fomrulas I have used everything what I need is a custom function in VBA which is embedded in the excel I have attached what we need is a further enhancement for this VBA code to take into account also criteria in selecting the unique values.

If you check worksheets Lights_Utilisation and Doors_Utilisation in column B:B you will notice that there are various values running across. What I need is for the macro function to check these values and return back the number of unique counts that correspond to Cyprus and Greece separately. So if the VBA works fine the correct number to be return is for Cyprus 365 unique instances (in cell D2 in worksheet Master Table) and for Greece 5063 (in cell D3 in worksheet Master Table). Pls let me know if I have clarified now a bit better and apologies if so far it was not clear.
thanks

Exactly right , when I say data I mean these numbers . For example check number "RO5781729798", this number can be found both in worksheet Lights_Utilisation but also on worksheet Doors_Utilisation , so the count function should only count it once and not twice. Let me know if clear now.

First of all pls allow me to express my gratitude for offering me this beautiful code and I confirm it works like a charm and returns the correct numbers. Now I would like to make a request and if you can support it's fine if you cannot I will again fully understand.
The vba code you have provided me it works just fine when I have 2 countries I am planning to insert more countries. To this extend is it possible to make this behave like a function instread of me having to play with the code each time I need to enter another country in the Master Table?