Count If with multiple criteria and unique values

[dixon1983]

Hi,
I dont know if this is possible or not but thought I'd ask anyway.

I have a sheet of data and want to run a count on the values in Column A with the following criteria:

- If the value in column B is repeated then the value in column A can only be counted once...
- Column C must equal 'M'.


Example:
A.....B.....C
1.....42....M
1.....42....M
2.....41....A
2.....41....M
1.....56....M


In this example 1 would be counted 2 times, and 2 would be counted once.

I have about 15 sheets of data like this and would like a master sheet to look into each sheet and give me a count of each so id prefer to not use the advanced filter => unique variables option if possible.

Any help would be greatly appreciated.

Dixon.

 

[Aladin Akyurek]

Thank you for the help you all posted in this thread, it saved me so much time and frustration!

Pleased to hear that. Thanks for providing feedback.

 

[PuGZoR]

Hi all,

Please be kind, this is my first post...

Firstly here is my data:

[TABLE="width: 425"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Customer[/TD]
[TD]Week Number[/TD]
[TD]Job ID[/TD]
[/TR]
[TR]
[TD]Customer A[/TD]
[TD]1[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]Customer A[/TD]
[TD]2[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]Customer A[/TD]
[TD]2[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]Customer A[/TD]
[TD]3[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]Customer A[/TD]
[TD]3[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]Customer B[/TD]
[TD]1[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]Customer B[/TD]
[TD]1[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]Customer B[/TD]
[TD]1[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]Customer B[/TD]
[TD]1[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]Customer B[/TD]
[TD]1[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]Customer B[/TD]
[TD]2[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]Customer C[/TD]
[TD]2[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]Customer C[/TD]
[TD]2[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]Customer C[/TD]
[TD]2[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]Customer C[/TD]
[TD]3[/TD]
[TD]X[/TD]
[/TR]
</tbody>[/TABLE]

What I want to do is fill in this table with the number of unique Job IDs per customer per week:

[TABLE="width: 485"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]Week Number:[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Customer A[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Customer B[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Customer C[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

Obviously I've put the numbers I'd expect in here, but I can't figure the formula to make it happen (my dataset is around 50,000 rows and there are about 1,400 unique job IDs).

Please help!

 

[Aladin Akyurek]

[...] (my dataset is around 50,000 rows and there are about 1,400 unique job IDs).
[...]

[TABLE="width: 622"]
<TBODY>[TR]
[TD="class: xl64, width: 128, bgcolor: white"]Customer
[/TD]
[TD="class: xl64, width: 143, bgcolor: white"]Week Number
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]Job ID
[/TD]
[TD="class: xl64, width: 114, bgcolor: white"]Concat
[/TD]
[TD="class: xl66, width: 38, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 150, bgcolor: white"]Week Number:
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]1
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]2
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]3
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 128, bgcolor: white"]Customer A
[/TD]
[TD="class: xl65, width: 143, bgcolor: white"]1
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Z
[/TD]
[TD="class: xl66, bgcolor: transparent"]Customer A|1
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 150, bgcolor: white"]Customer A
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]1
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]1
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]1
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 128, bgcolor: white"]Customer A
[/TD]
[TD="class: xl65, width: 143, bgcolor: white"]2
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Z
[/TD]
[TD="class: xl66, bgcolor: transparent"]Customer A|2
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 150, bgcolor: white"]Customer B
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]3
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]1
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]0
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 128, bgcolor: white"]Customer A
[/TD]
[TD="class: xl65, width: 143, bgcolor: white"]2
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Z
[/TD]
[TD="class: xl66, bgcolor: transparent"]Customer A|2
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 150, bgcolor: white"]Customer C
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]0
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]2
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]1
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 128, bgcolor: white"]Customer A
[/TD]
[TD="class: xl65, width: 143, bgcolor: white"]3
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Z
[/TD]
[TD="class: xl66, bgcolor: transparent"]Customer A|3
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 128, bgcolor: white"]Customer A
[/TD]
[TD="class: xl65, width: 143, bgcolor: white"]3
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Z
[/TD]
[TD="class: xl66, bgcolor: transparent"]Customer A|3
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 128, bgcolor: white"]Customer B
[/TD]
[TD="class: xl65, width: 143, bgcolor: white"]1
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Z
[/TD]
[TD="class: xl66, bgcolor: transparent"]Customer B|1
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 128, bgcolor: white"]Customer B
[/TD]
[TD="class: xl65, width: 143, bgcolor: white"]1
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Z
[/TD]
[TD="class: xl66, bgcolor: transparent"]Customer B|1
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 128, bgcolor: white"]Customer B
[/TD]
[TD="class: xl65, width: 143, bgcolor: white"]1
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Y
[/TD]
[TD="class: xl66, bgcolor: transparent"]Customer B|1
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 128, bgcolor: white"]Customer B
[/TD]
[TD="class: xl65, width: 143, bgcolor: white"]1
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Y
[/TD]
[TD="class: xl66, bgcolor: transparent"]Customer B|1
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 128, bgcolor: white"]Customer B
[/TD]
[TD="class: xl65, width: 143, bgcolor: white"]1
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]X
[/TD]
[TD="class: xl66, bgcolor: transparent"]Customer B|1
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 128, bgcolor: white"]Customer B
[/TD]
[TD="class: xl65, width: 143, bgcolor: white"]2
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Z
[/TD]
[TD="class: xl66, bgcolor: transparent"]Customer B|2
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 128, bgcolor: white"]Customer C
[/TD]
[TD="class: xl65, width: 143, bgcolor: white"]2
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Z
[/TD]
[TD="class: xl66, bgcolor: transparent"]Customer C|2
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 128, bgcolor: white"]Customer C
[/TD]
[TD="class: xl65, width: 143, bgcolor: white"]2
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Z
[/TD]
[TD="class: xl66, bgcolor: transparent"]Customer C|2
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 128, bgcolor: white"]Customer C
[/TD]
[TD="class: xl65, width: 143, bgcolor: white"]2
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]X
[/TD]
[TD="class: xl66, bgcolor: transparent"]Customer C|2
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 128, bgcolor: white"]Customer C
[/TD]
[TD="class: xl65, width: 143, bgcolor: white"]3
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]X
[/TD]
[TD="class: xl66, bgcolor: transparent"]Customer C|3
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

Given the amount of the data you have, we will trade off some memory against speed...

D2, just enter and copy down:

=A2&"|"&B2

G2, control+shift+enter, not just enter, copy across, and down:

=SUM(IF(FREQUENCY(IF($D$2:$D$16=$F2&"|"&G$1,MATCH($C$2:$C$16,$C$2:$C$16,0)),
  ROW($C$2:$C$16)-ROW($C$2)+1),1))

 

[PuGZoR]

[TABLE="width: 622"]
<tbody>[TR]
[TD="class: xl64, width: 128, bgcolor: white"]Customer[/TD]
[TD="class: xl64, width: 143, bgcolor: white"]Week Number[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]Job ID[/TD]
[TD="class: xl64, width: 114, bgcolor: white"]Concat[/TD]
[TD="class: xl66, width: 38, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 150, bgcolor: white"]Week Number:[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]1[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]2[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]3[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 128, bgcolor: white"]Customer A[/TD]
[TD="class: xl65, width: 143, bgcolor: white"]1[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Z[/TD]
[TD="class: xl66, bgcolor: transparent"]Customer A|1[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 150, bgcolor: white"]Customer A[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]1[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]1[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 128, bgcolor: white"]Customer A[/TD]
[TD="class: xl65, width: 143, bgcolor: white"]2[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Z[/TD]
[TD="class: xl66, bgcolor: transparent"]Customer A|2[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 150, bgcolor: white"]Customer B[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]3[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]1[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]0[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 128, bgcolor: white"]Customer A[/TD]
[TD="class: xl65, width: 143, bgcolor: white"]2[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Z[/TD]
[TD="class: xl66, bgcolor: transparent"]Customer A|2[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 150, bgcolor: white"]Customer C[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]0[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]2[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 128, bgcolor: white"]Customer A[/TD]
[TD="class: xl65, width: 143, bgcolor: white"]3[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Z[/TD]
[TD="class: xl66, bgcolor: transparent"]Customer A|3[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 128, bgcolor: white"]Customer A[/TD]
[TD="class: xl65, width: 143, bgcolor: white"]3[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Z[/TD]
[TD="class: xl66, bgcolor: transparent"]Customer A|3[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 128, bgcolor: white"]Customer B[/TD]
[TD="class: xl65, width: 143, bgcolor: white"]1[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Z[/TD]
[TD="class: xl66, bgcolor: transparent"]Customer B|1[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 128, bgcolor: white"]Customer B[/TD]
[TD="class: xl65, width: 143, bgcolor: white"]1[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Z[/TD]
[TD="class: xl66, bgcolor: transparent"]Customer B|1[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 128, bgcolor: white"]Customer B[/TD]
[TD="class: xl65, width: 143, bgcolor: white"]1[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Y[/TD]
[TD="class: xl66, bgcolor: transparent"]Customer B|1[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 128, bgcolor: white"]Customer B[/TD]
[TD="class: xl65, width: 143, bgcolor: white"]1[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Y[/TD]
[TD="class: xl66, bgcolor: transparent"]Customer B|1[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 128, bgcolor: white"]Customer B[/TD]
[TD="class: xl65, width: 143, bgcolor: white"]1[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]X[/TD]
[TD="class: xl66, bgcolor: transparent"]Customer B|1[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 128, bgcolor: white"]Customer B[/TD]
[TD="class: xl65, width: 143, bgcolor: white"]2[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Z[/TD]
[TD="class: xl66, bgcolor: transparent"]Customer B|2[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 128, bgcolor: white"]Customer C[/TD]
[TD="class: xl65, width: 143, bgcolor: white"]2[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Z[/TD]
[TD="class: xl66, bgcolor: transparent"]Customer C|2[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 128, bgcolor: white"]Customer C[/TD]
[TD="class: xl65, width: 143, bgcolor: white"]2[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Z[/TD]
[TD="class: xl66, bgcolor: transparent"]Customer C|2[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 128, bgcolor: white"]Customer C[/TD]
[TD="class: xl65, width: 143, bgcolor: white"]2[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]X[/TD]
[TD="class: xl66, bgcolor: transparent"]Customer C|2[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 128, bgcolor: white"]Customer C[/TD]
[TD="class: xl65, width: 143, bgcolor: white"]3[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]X[/TD]
[TD="class: xl66, bgcolor: transparent"]Customer C|3[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

Given the amount of the data you have, we will trade off some memory against speed...

D2, just enter and copy down:

=A2&"|"&B2

G2, control+shift+enter, not just enter, copy across, and down:

=SUM(IF(FREQUENCY(IF($D$2:$D$16=$F2&"|"&G$1,MATCH($C$2:$C$16,$C$2:$C$16,0)),
  ROW($C$2:$C$16)-ROW($C$2)+1),1))

I can't believe I didn't think of that... Thank you so much! Worked 100%.

 

[Aladin Akyurek]

I can't believe I didn't think of that... Thank you so much! Worked 100%.

You are welcome. Thanks for providing feedback.

 

[Kevin SantaBarbara]

Define EmpID as referring to:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(9.99999999999999E+307,Sheet1!$A:$A))

Dept as:

=Sheet1!$B$2:INDEX(Sheet1!$B:$B,MATCH(9.99999999999999E+307,Sheet1!$B:$B))

Ivec as:

=ROW(EmpID)-ROW(INDEX(EmpID,1,1))+1

See:
https://dl.dropboxusercontent.com/u...lUniqueCount DynamicSetUp Sean Christian.xlsx

OK, I am having the same problem with Excel resources (very large database) and want to try this method, but how do I do this part? I do not see this code anywhere in the attached spreadsheet. Thx

 

[Aladin Akyurek]

OK, I am having the same problem with Excel resources (very large database) and want to try this method, but how do I do this part? I do not see this code anywhere in the attached spreadsheet. Thx

Try the option Formulas | Name Manager from the ribbon. Name Manger will show all of these definitions.

 

[Kevin SantaBarbara]

Great, thank you. Now what I get is Excel entering a "not responding" state. My conclusion was that its a very large data set and this might take a while. I let it run for a couple of hours, but never recovered. I don't know whether I've done something wrong or if the job I'm asking Excel to do is too large. My data set has 3 columns and over 500,000 rows (all data numeric). If that's just way to big, I'll stop trying to figure if I've done something wrong. Any idea of Excel's limit for this kind of array formula?

 

[Kevin SantaBarbara]

Update: Also tried copying my data into the spreadsheet that was linked earlier. Same result. Excel spins and spins. Concluding that my data set is too large for this approach. Any other ideas?

 

[Aladin Akyurek]

Update: Also tried copying my data into the spreadsheet that was linked earlier. Same result. Excel spins and spins. Concluding that my data set is too large for this approach. Any other ideas?

Are you able to post the formula you have?