VBA Code to get distinct count with muliple criteria

[Wafee]

Can some one help me with a VBA code that gives distinct count of data in in "I" Column of sheet1 (dynamic) after filtering "Temp" and "Tem" in A column and "MX" in E column. I have a code as below which gives distinct count from "I" column but not able to add the filters. Would be great if get a code simpler than this.

Sub Dcount()
Dim Lastrow As Long
Rng As Range
List As Object
Listcount As Long
Lastrow = Cells(Rows.Count,"A").End(xlUp).Row
Set List = Createobject("Scripting.Dictionary)
For Each Rng In Range("I2:I", & Lastrow)
If Not List.Exists(Rng.Value) Then List.Add Rng.Value, Nothing
Next
Listcount = List.Count
Range("M2").Value = ListCount

End Sub

 

[Wafee]

What is the actual value in M2 when you get the error?

It the count. 223 in my case actually. It is the distinct count from column I. Now I also want that list of 223 unique list along with the count.

 

[Fluff]

Oops, it should have been

Range("S2").Resize(List.Count).Value = Application.Transpose(List.Keys)

 

[Wafee]

Oops, it should have been

Range("S2").Resize(List.Count).Value = Application.Transpose(List.Keys)

Works Perfectly mate. You are a legend and I promise this will be the last thing I am gonna ask. Can we able to get sum of hours which is in in R column for the above list alone?

 

[Fluff]

How about

Sub Dcount()
Dim Lastrow As Long
Dim Rng As Range
Dim List As Object
Dim Listcount As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set List = CreateObject("Scripting.Dictionary")
With Range("A1:I1")
   .AutoFilter 1, "Temp", xlOr, "tem"
   .AutoFilter 5, "MX"
End With
For Each Rng In Range("I2:I" & Lastrow).SpecialCells(xlVisible)
   List(Rng.Value) = List(Rng.Value) + Rng.Offset(, 9).Value
Next Rng
ActiveSheet.AutoFilterMode = False
Listcount = List.Count
Range("M2").Value = Listcount
Range("S2").Resize(List.Count, 2).Value = Application.Transpose(Array(List.Keys, List.Items))

End Sub

 

[Wafee]

How about

Sub Dcount()
Dim Lastrow As Long
Dim Rng As Range
Dim List As Object
Dim Listcount As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set List = CreateObject("Scripting.Dictionary")
With Range("A1:I1")
   .AutoFilter 1, "Temp", xlOr, "tem"
   .AutoFilter 5, "MX"
End With
For Each Rng In Range("I2:I" & Lastrow).SpecialCells(xlVisible)
   List(Rng.Value) = List(Rng.Value) + Rng.Offset(, 9).Value
Next Rng
ActiveSheet.AutoFilterMode = False
Listcount = List.Count
Range("M2").Value = Listcount
Range("S2").Resize(List.Count, 2).Value = Application.Transpose(Array(List.Keys, List.Items))

End Sub

Thanks again Fulff, Here we are finding the list and making it in to an array. For the values in the array I want sum of values that are in R column. To re-iterarate I want Sum from R column for the above list or array which we obtained. So I am thinking on the lines of looping through each item from the list and getting their value from R column.

 

[Fluff]

That does give you the sum of each value in the list.

 

[Wafee]

I want to get total sum from R colum for all the items from the array with different criteria. So my ask here is once we figured out the list or array of values which are from I column can we filter "Yes" from K column and get the total sum from R column.

 

[Fluff]

That is a totally different question & so needs a new thread. Thanks

 

[Wafee]

That is a totally different question & so needs a new thread. Thanks

Sure mate, thank you so much??

 

[Fluff]

You're welcome.