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!

 

[skitalez]

Roger, thanks for Pivot Table option, I've tried it already.
The problem is that the count of "worker" field counts ALL workers from given departments and given months (counts all rows), not the unique workers.
Pivot table would be better decision, since it work fast compare to FREQUENCY array formula

 

[Aladin Akyurek]

Why don't you answer post #6?

 

[Roger Govier]

Hi

You can achieve a Unique count with a double Pivot - Pivot the Pivot Table.
I wrote an article on the subject which my MVP colleague Debra Dalgleish kindly posted on her website.
http://www.contextures.com/pivottablecountunique.html

 

[skitalez]

Aladin, I've just finish counting the data for 2015 months in PC with i7, 8 processors and 16 MB RAM.
On my PC, Excel just crashed...

 

[skitalez]

Thank you Aladin , it still works and gives the result. And I also will try to do Pivot on Pivot table according to Roger's advice

 

[Aladin Akyurek]

Thank you Aladin , it still works and gives the result. And I also will try to do Pivot on Pivot table according to Roger's advice

Great. By all means, try the pivot procedure too. How about the VBA MickG has jotted down for you?

Here is an improvement on the formula approach...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][td]

L​

[/td][/tr]
[tr][td]

1​

[/td][td]Day[/td][td]Month[/td][td]Dep#[/td][td]Worker[/td][td]Sales[/td][td]concat[/td][td]Dep/Month [/td][td]

1.2015​

[/td][td]

2.2015​

[/td][td]

3.2015​

[/td][/tr]


[tr][td]

2​

[/td][td]

1​

[/td][td]

1.2015​

[/td][td]Dep#3[/td][td]A[/td][td]

5​

[/td][td]Dep#3|1.2015[/td][td]Dep#1[/td][td]

2​

[/td][td]

0​

[/td][td]

2​

[/td][/tr]


[tr][td]

3​

[/td][td]

2​

[/td][td]

1.2015​

[/td][td]Dep#4[/td][td]B[/td][td]

8​

[/td][td]Dep#4|1.2015[/td][td]Dep#2[/td][td]

2​

[/td][td]

0​

[/td][td]

0​

[/td][/tr]


[tr][td]

4​

[/td][td]

3​

[/td][td]

1.2015​

[/td][td]Dep#7[/td][td]D[/td][td]

12​

[/td][td]Dep#7|1.2015[/td][td]Dep#3[/td][td]

2​

[/td][td]

1​

[/td][td]

0​

[/td][/tr]


[tr][td]

5​

[/td][td]

4​

[/td][td]

1.2015​

[/td][td]Dep#7[/td][td]A[/td][td]

33​

[/td][td]Dep#7|1.2015[/td][td]Dep#4[/td][td]

1​

[/td][td]

0​

[/td][td]

1​

[/td][/tr]


[tr][td]

6​

[/td][td]

5​

[/td][td]

1.2015​

[/td][td]Dep#3[/td][td]V[/td][td]

19​

[/td][td]Dep#3|1.2015[/td][td]Dep#5[/td][td]

0​

[/td][td]

2​

[/td][td]

0​

[/td][/tr]


[tr][td]

7​

[/td][td]

6​

[/td][td]

1.2015​

[/td][td]Dep#1[/td][td]G[/td][td]

81​

[/td][td]Dep#1|1.2015[/td][td]Dep#6[/td][td]

0​

[/td][td]

0​

[/td][td]

1​

[/td][/tr]


[tr][td]

8​

[/td][td]

7​

[/td][td]

1.2015​

[/td][td]Dep#2[/td][td]H[/td][td]

14​

[/td][td]Dep#2|1.2015[/td][td]Dep#7[/td][td]

2​

[/td][td]

0​

[/td][td]

1​

[/td][/tr]


[tr][td]

9​

[/td][td]

8​

[/td][td]

1.2015​

[/td][td]Dep#2[/td][td]P[/td][td]

57​

[/td][td]Dep#2|1.2015[/td][td]Dep#8[/td][td]

0​

[/td][td]

2​

[/td][td]

0​

[/td][/tr]


[tr][td]

10​

[/td][td]

9​

[/td][td]

1.2015​

[/td][td]Dep#1[/td][td]R[/td][td]

63​

[/td][td]Dep#1|1.2015[/td][td]Dep#9[/td][td]

1​

[/td][td]

0​

[/td][td]

0​

[/td][/tr]


[tr][td]

11​

[/td][td]

10​

[/td][td]

1.2015​

[/td][td]Dep#9[/td][td]V[/td][td]

95​

[/td][td]Dep#9|1.2015[/td][td]Dep#10[/td][td]

0​

[/td][td]

1​

[/td][td]

2​

[/td][/tr]


[tr][td]

12​

[/td][td]

11​

[/td][td]

1.2015​

[/td][td]Dep#4[/td][td]B[/td][td]

21​

[/td][td]Dep#4|1.2015[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

13​

[/td][td]

1​

[/td][td]

2.2015​

[/td][td]Dep#3[/td][td]A[/td][td]

2​

[/td][td]Dep#3|2.2015[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

14​

[/td][td]

2​

[/td][td]

2.2015​

[/td][td]Dep#8[/td][td]R[/td][td]

84​

[/td][td]Dep#8|2.2015[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

15​

[/td][td]

3​

[/td][td]

2.2015​

[/td][td]Dep#5[/td][td]F[/td][td]

36​

[/td][td]Dep#5|2.2015[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

16​

[/td][td]

4​

[/td][td]

2.2015​

[/td][td]Dep#5[/td][td]Q[/td][td]

64​

[/td][td]Dep#5|2.2015[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

17​

[/td][td]

5​

[/td][td]

2.2015​

[/td][td]Dep#10[/td][td]W[/td][td]

66​

[/td][td]Dep#10|2.2015[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

18​

[/td][td]

6​

[/td][td]

2.2015​

[/td][td]Dep#8[/td][td]F[/td][td]

46​

[/td][td]Dep#8|2.2015[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

19​

[/td][td]

7​

[/td][td]

3.2015​

[/td][td]Dep#10[/td][td]H[/td][td]

32​

[/td][td]Dep#10|3.2015[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

20​

[/td][td]

8​

[/td][td]

3.2015​

[/td][td]Dep#1[/td][td]R[/td][td]

19​

[/td][td]Dep#1|3.2015[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

21​

[/td][td]

9​

[/td][td]

3.2015​

[/td][td]Dep#1[/td][td]Y[/td][td]

92​

[/td][td]Dep#1|3.2015[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

22​

[/td][td]

10​

[/td][td]

3.2015​

[/td][td]Dep#10[/td][td]A[/td][td]

74​

[/td][td]Dep#10|3.2015[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

23​

[/td][td]

11​

[/td][td]

3.2015​

[/td][td]Dep#6[/td][td]S[/td][td]

55​

[/td][td]Dep#6|3.2015[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

24​

[/td][td]

12​

[/td][td]

3.2015​

[/td][td]Dep#4[/td][td]D[/td][td]

70​

[/td][td]Dep#4|3.2015[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

25​

[/td][td]

13​

[/td][td]

3.2015​

[/td][td]Dep#7[/td][td]B[/td][td]

5​

[/td][td]Dep#7|3.2015[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In F2 just enter and copy down:

=C2&"|"&B2

<strike></strike>


In J2 control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($F$2:$F$25=$I2&"|"&J$1,MATCH($D$2:$D$25,$D$2:$D$25,0)),Ivec),1))

Ivec is defined in Name Manager as referring to:

=ROW(Sheet1!$F$2:$F$25)-ROW(Sheet1!$F$2)+1

Adjust to suit.

Note. We are trading off memory against speed here.

 

[skitalez]

Aladin, tried both methods you've proposed - the result is the same in time that my Excel need to calculate each cell. Approx 30 seconds.
Tried pivot on pivot table, that Roger proposed link to.
Get confused in it, cause in pivot 1 I need to put Dep, month and worker in row labels. And when forming Pivot 2 I've tried different combinations of what field where to put, but it doesn't work.
I've also tried to put month in column labels but also with no result.
Haven't yet tried VBA that MickG offered...

 

[skitalez]

Roger, in downloadable example there is only one filter criteria - region. In my pivot there are two - dep and month. How it can be solved?

 

[Aladin Akyurek]

Aladin, tried both methods you've proposed - the result is the same in time that my Excel need to calculate each cell. Approx 30 seconds.
[...]

There should be a significant difference...

 

[skitalez]

Aladin, I put it on timer several times - 30-35 seconds on both methods