Count unique values with criteria

[skitalez]

Hi everyone,
I have a list of data, that goes like this:
Day Month Department Worker Sales
1 1.2015 Department 3 A 5
2 1.2015 Department 4 B 8
3 1.2015 Department 7 D 12
4 1.2015 Department 7 A 33
5 1.2015 Department 3 V 19
6 1.2015 Department 1 G 81
7 1.2015 Department 2 H 14
8 1.2015 Department 2 P 57
9 1.2015 Department 1 R 63
10 1.2015 Department 9 V 95
11 1.2015 Department 4 B 21
1 2.2015 Department 3 A 2
2 2.2015 Department 8 R 84
3 2.2015 Department 5 F 36
4 2.2015 Department 5 Q 64
5 2.2015 Department 10 W 66
6 2.2015 Department 8 F 46
7 3.2015 Department 10 H 32
8 3.2015 Department 1 R 19
9 3.2015 Department 1 Y 92
10 3.2015 Department 10 A 74
11 3.2015 Department 6 S 55
12 3.2015 Department 4 D 70
13 3.2015 Department 7 B 5

I'm trying to answer the question "how many different workers were in each month in each department?" in the following table (the same worker can work in different departments):
Dep/Month 1 .2015 2 .2015 3 .2015
Department 1
Department 2
Department 3
Department 4
Department 5
Department 6
Department 7
Department 8
Department 9
Department 10

Thanks for help!

 

[seguin85]

Countifs would be your best bet.

=countifs($B$1:$B$100,$H3,$A$1:$A$1000,I$3)

When the range B1:B100 holds Department #, A1:A100 holds month.

 

[Aladin Akyurek]

Control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF(1-(worker=""),IF(department="department 1",IF(month="1.2015",MATCH(worker,worker,0)))),ROW(worker)-ROW(INDEX(worker,1,1))+1),1))

 

[MickG]

Try this for Results on sheet2, starting "A1".

[COLOR="Navy"]Sub[/COLOR] MG18Dec38
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range, Rng [COLOR="Navy"]As[/COLOR] Range, Dic [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] k [COLOR="Navy"]As[/COLOR] Variant, Col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] p [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("Scripting.Dictionary")
Dic.CompareMode = 1
   
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            [COLOR="Navy"]If[/COLOR] Not Dic.exists(Dn.Offset(, 2).Value) [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Set[/COLOR] Dic(Dn.Offset(, 2).Value) = CreateObject("Scripting.Dictionary")
            [COLOR="Navy"]End[/COLOR] If
        
        [COLOR="Navy"]If[/COLOR] Not Dic(Dn.Offset(, 2).Value).exists(Dn.Offset(, 1).Value) [COLOR="Navy"]Then[/COLOR]
                Dic(Dn.Offset(, 2).Value).Add (Dn.Offset(, 1).Value), Dn.Offset(, 3)
        [COLOR="Navy"]Else[/COLOR]
                Dic(Dn.Offset(, 2).Value).Item(Dn.Offset(, 1).Value) = _
                Dic(Dn.Offset(, 2).Value).Item(Dn.Offset(, 1).Value) & "," & Dn.Offset(, 3).Value
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn


ReDim Ray(1 To Dic.Count + 1, 1 To 1)
    Ray(1, 1) = "Dep/Month"
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] Dic.Keys
    Rw = Split(k, " ")(1)
    Ray(Rw + 1, 1) = k
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] p [COLOR="Navy"]In[/COLOR] Dic(k)
            Col = Split(p, ".")(0)
            [COLOR="Navy"]If[/COLOR] UBound(Ray, 2) <= Col + 1 [COLOR="Navy"]Then[/COLOR] ReDim Preserve Ray(1 To Dic.Count + 1, 1 To Col + 1)
            Ray(1, Col + 1) = p
            Ray(Rw + 1, Col + 1) = Dic(k).Item(p)
        [COLOR="Navy"]Next[/COLOR] p
[COLOR="Navy"]Next[/COLOR] k
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(Dic.Count + 1, UBound(Ray, 2))
    .Value = Ray
    .Columns.AutoFit
    .Borders.Weight = 2
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[skitalez]

Thanks, Aladin.
Am I getting you right?
{=SUM(IF(FREQUENCY(IF(1-($U$2:$U$54456=""),IF($K$2:$K$54456=$A54459,IF($Q$2:$Q$54456=B$54458,MATCH($U$2:$U$54456,$U$2:$U$54456,0)))),ROW($U$2:$U$54456)-ROW(INDEX($U$2:$U$54456,1,1))+1),1))}
U - is the column of worker's data
K - departments data
O - months data
$A54459 - is the Department 1 cell
B$54458 - is the 01.2015 cell
But the array formula returns 0 (zeros) in all cells after drag and drop...

 

[Aladin Akyurek]

Thanks, Aladin.
Am I getting you right?
{=SUM(IF(FREQUENCY(IF(1-($U$2:$U$54456=""),IF($K$2:$K$54456=$A54459,IF($Q$2:$Q$54456=B$54458,MATCH($U$2:$U$54456,$U$2:$U$54456,0)))),ROW($U$2:$U$54456)-ROW(INDEX($U$2:$U$54456,1,1))+1),1))}
U - is the column of worker's data
K - departments data
O - months data
$A54459 - is the Department 1 cell
B$54458 - is the 01.2015 cell
But the array formula returns 0 (zeros) in all cells after drag and drop...

You got it right.

What does this give?

=B$54458&""

and this?

=$Q$2&""

 

[skitalez]

{=SUM(IF(FREQUENCY(IF(1-($U$2:$U$54456=""),IF($K$2:$K$54456=$A54459&"",IF($Q$2:$Q$54456=B$54458&"",MATCH($U$2:$U$54456,$U$2:$U$54456,0)))),ROW($U$2:$U$54456)-ROW(INDEX($U$2:$U$54456,1,1))+1),1))}
Still got zeros...

 

[skitalez]

Sorry, since the file is big enough, I can't drag and drop this array formula, otherwise I got zeros. So I have to copy cell by cell in the table and wait.
But it works!

 

[Aladin Akyurek]

You got it right.

What does this give?

=B$54458&""

and this?

=$Q$2&""

{=SUM(IF(FREQUENCY(IF(1-($U$2:$U$54456=""),IF($K$2:$K$54456=$A54459&"",IF($Q$2:$Q$54456=B$54458&"",MATCH($U$2:$U$54456,$U$2:$U$54456,0)))),ROW($U$2:$U$54456)-ROW(INDEX($U$2:$U$54456,1,1))+1),1))}
Still got zeros...

Interested in the results of the those formulas, not in plugging them in the frequency formula. Would you post what they give?

 

[Roger Govier]

Hi

I think I would just use a Pivot Table.
Turn your data into a Table first by placing cursor within dataset and Insert > Table > my table has headers. You can lerave the name as the default of Table1
Then with your cursor within your Table >Insert Pivot Table >OK
Drag Month to the Columns Area
Drag Department to the Rows area
Drag Worker to the Data area and because it is Text it will automatically default to Count

There you have your result.

Note your Departments will be sorted Department 1, Department 10, Department 2.
If you wish to avoid this, then amend your data such that Department value have dual digit, Department 01 etc.

As you add more data to your Source table, so it will be reflected in your Pivot Table after your Refresh the PT.
(You can have the PT refresh automatically each time you active the sheet with a tiny bit of VBA code if you wish)