Counting Unique Values Given Criteria
 

My data table is setup like so:

93000 A Tech1
93000 B Tech1
93000 C Tech2
93000 A Tech2
93000 A Tech1
93000 A Tech1
93000 A Tech1
93100 B Tech1
93100 C Tech1
93100 C Tech1
93100 A Tech1
93200 B Tech2
93200 C Tech2
93200 C Tech2
93200 A Tech2
93200 A Tech2
93200 A Tech2
93200 A Tech2


I amtrying to create a table that counts the unique values like so:

Tech1 Tech2
93000 2 2
93100 3 0
93200 0 3


Thank you in advance if this is poossible.

Assumptions:

A1:C18 contains your data

E2:E4 contains 93000, 93100, and 93200

F1:G1 contains Tech1 and Tech2


Formula:

F2, copied down and over to the next column...

=SUM(IF(FREQUENCY(IF(($A$1:$A$18=$E2)*($C$1:$C$18= F$1),MATCH($B$1:$B$18,$
B$1:$B$18,0)),ROW($C$1:$C$18)-ROW($C$1)+1)0,1))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
"carl" wrote:

My data table is setup like so:

93000 A Tech1
93000 B Tech1
93000 C Tech2
93000 A Tech2
93000 A Tech1
93000 A Tech1
93000 A Tech1
93100 B Tech1
93100 C Tech1
93100 C Tech1
93100 A Tech1
93200 B Tech2
93200 C Tech2
93200 C Tech2
93200 A Tech2
93200 A Tech2
93200 A Tech2
93200 A Tech2


I amtrying to create a table that counts the unique values like so:

Tech1 Tech2
93000 2 2
93100 3 0
93200 0 3


Thank you in advance if this is poossible.

With your data in A1 to C18:
G1: =SUMPRODUCT(--($A1=$A$1:$A1))
H1: =SUMPRODUCT(--($A$1:$A$18<=$A1),--($G$1:$G$18=1))
J1: =ROW(I1)-ROW($I$1)+1
K1: =SUMPRODUCT($A$1:$A$18,--($H$1:$H$18=J1),--($G$1:$G$18=1))
Copy G1 to K1 down.
"carl" wrote:

My data table is setup like so:

93000 A Tech1
93000 B Tech1
93000 C Tech2
93000 A Tech2
93000 A Tech1
93000 A Tech1
93000 A Tech1
93100 B Tech1
93100 C Tech1
93100 C Tech1
93100 A Tech1
93200 B Tech2
93200 C Tech2
93200 C Tech2
93200 A Tech2
93200 A Tech2
93200 A Tech2
93200 A Tech2


I amtrying to create a table that counts the unique values like so:

Tech1 Tech2
93000 2 2
93100 3 0
93200 0 3


Thank you in advance if this is poossible.