Count unique values based on criteria from other column

[behedwin]

Hi
I have a sheet that looks like this

color | number

now i want to count how many unique numbers i have of one color

So the values can be like this

in color column
blue
red
yellow
blue
blue
yellow
red
yellow
yellow


in the number column
123
22
123
143
12
132
123
22
22
22


So how many unique values do i have of the color Blue for example

My solution is this:
https://www.excel-easy.com/examples/count-unique-values.html

=SUM(1/COUNTIF(A:A;"blue";B:B;B:B))

But i dont get it to work

Any suggetions... i just get the result 0
I do press ctrl+shift+enter

 

[Legacy 436357]

I need to revise sorry

 

[Legacy 436357]

This solution should work:

Excel Workbook
	A	B	C	D	E
1	Colors	Numbers		Color	Frequency
2	blue	123		blue	3
3	red	22		red	2
4	yellow	123		yellow	3
5	blue	143
6	blue	12
7	yellow	132
8	red	123
9	yellow	22
10	yellow	22
11	yellow	22
12	blue	12
13	yellow	22
14	red	123
Sheet1

 

[behedwin]

This solution should work:

Sheet1

	A	B	C	D	E
Colors	Numbers		Color	Frequency
blue		blue
red		red
yellow		yellow
blue
blue
yellow
red
yellow
yellow
yellow
blue
yellow
red

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]123[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]22[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]123[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]143[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]12[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]132[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]123[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]22[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]22[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]22[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]22[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]123[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula E2 {=SUM(IF(FREQUENCY(IF(A$2:A$15=D2,MATCH(B$2:B$15,B$2:B$15,0)),ROW(B$2:B$15)-ROW(B$2)+1),1))} E3 {=SUM(IF(FREQUENCY(IF(A$2:A$15=D3,MATCH(B$2:B$15,B$2:B$15,0)),ROW(B$2:B$15)-ROW(B$2)+1),1))} E4 {=SUM(IF(FREQUENCY(IF(A$2:A$15=D4,MATCH(B$2:B$15,B$2:B$15,0)),ROW(B$2:B$15)-ROW(B$2)+1),1))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

What am i missing here... it seems to work for you.
I modified it a little bit, but must have broken the line....
{=SUM(IF(FREQUENCY(IF('TotalAbsence'!A:A="Blue";MATCH('TotalAbsence'!D:D;0));ROW('TotalAbsence'!D:D)-ROW('TotalAbsence'!$D$2)+1);1))}

A = column with colors
D = column with numbers
TotalAbsence = a sheet name where the data is and i put this line in a new sheet.


I just get #VALUE in the cell after activating the code

 

[Legacy 436357]

If you want to pull from another sheet try:

=SUM(IF(FREQUENCY(IF(Sheet1!A$2:A$15=D2,MATCH(Sheet1!B$2:B$15,Sheet1!B$2:B$15,0)),ROW(Sheet1!B$2:B$15)-ROW(Sheet1!B$2)+1),1))

Excel Workbook
	D	E
1	Color	Frequency
2	blue	3
3	red	2
4	yellow	3
Sheet2

 

[behedwin]

If you want to pull from another sheet try:

=SUM(IF(FREQUENCY(IF(Sheet1!A$2:A$15=D2,MATCH(Sheet1!B$2:B$15,Sheet1!B$2:B$15,0)),ROW(Sheet1!B$2:B$15)-ROW(Sheet1!B$2)+1),1))

Sheet2

	D	E
Color	Frequency
blue
red
yellow

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:70.4px;"><col style="width:70.4px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: right"]3[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula E2 {=SUM(IF(FREQUENCY(IF(Sheet1!A$2:A$15=D2,MATCH(Sheet1!B$2:B$15,Sheet1!B$2:B$15,0)),ROW(Sheet1!B$2:B$15)-ROW(Sheet1!B$2)+1),1))} E3 {=SUM(IF(FREQUENCY(IF(Sheet1!A$2:A$15=D3,MATCH(Sheet1!B$2:B$15,Sheet1!B$2:B$15,0)),ROW(Sheet1!B$2:B$15)-ROW(Sheet1!B$2)+1),1))} E4 {=SUM(IF(FREQUENCY(IF(Sheet1!A$2:A$15=D4,MATCH(Sheet1!B$2:B$15,Sheet1!B$2:B$15,0)),ROW(Sheet1!B$2:B$15)-ROW(Sheet1!B$2)+1),1))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thank you, i got it to work

 

[Legacy 436357]

Glad to help!