Counting uniique items with criteria

[Blunder1]

Hi,

Any help would be appreciated on this as i want to move away from functions.

Basically, i would like to add vba code, simialr to the countif one below, that will count the number of unique items in column B on sheets 2 (named wt) if the the cell value in columns c is equal to the cell value in sheets 1 (named ws) cell b6 (these are text)

ws.Range("d7") = WorksheetFunction.CountIf(wt.Range("D:D"), ws.Range("b6").Value)

Then in another cell on sheet 1 i would like to know what the highest unique item is ( i.e if Dog is the most frequent then the cell value in sheets 1 cell d8 = Dog)..

Thanks in advance

Blunder1

 

[texasalynn]

take a look at Chip Pearson's article on counting unique entries

 

[Blunder1]

Thanks, but i dont think i can adapt this for my specific problem

 

[Marcelo Branco]

Hi,

Assuming your data in Sheet2 in B1:C100 maybe this

Set ws = Sheets("Sheet1")
 
'Counting Unique in Sheet2!B1:B100 if Sheet2!C1:C100 = Sheet1!B6
ws.Range("D7") = Evaluate("=SUM(IF(FREQUENCY(IF(Sheet2!B1:B100<>"""",IF(Sheet2!C1:C100=Sheet1!B6," _
    & "MATCH(""~""&Sheet2!B1:B100,Sheet2!B1:B100&"""",0))),ROW(B1:B20)-ROW(B1)+1),1))")
 
'Highest count in Sheet2!C1:C100
ws.Range("D8") = Evaluate("=INDEX(Sheet2!C1:C100,MATCH(1,IF(COUNTIF(Sheet2!C1:C100,Sheet2!C1:C100)" _
    & "=MAX(COUNTIF(Sheet2!C1:C100,Sheet2!C1:C100)),1),0))")

HTH

M.

 

[Marcelo Branco]

oops...There is a typo in the first formula

The correct is

ws.Range("D7") = Evaluate("=SUM(IF(FREQUENCY(IF(Sheet2!B1:B100<>"""",IF(Sheet2!C1:C100=Sheet1!B6," _
        & "MATCH(""~""&Sheet2!B1:B100,Sheet2!B1:B100&"""",0))),ROW(B1:B100)-ROW(B1)+1),1))")

M.