Count unique values based on criteria from other column

behedwin

Active Member
Joined
Dec 10, 2014
Messages
399
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
This solution should work:

Excel Workbook
ABCDE
1ColorsNumbersColorFrequency
2blue123blue3
3red22red2
4yellow123yellow3
5blue143
6blue12
7yellow132
8red123
9yellow22
10yellow22
11yellow22
12blue12
13yellow22
14red123
Sheet1
 
Upvote 0
This solution should work:

Sheet1

ABCDE
1ColorsNumbersColorFrequency
2blue123blue3
3red22red2
4yellow123yellow3
5blue143
6blue12
7yellow132
8red123
9yellow22
10yellow22
11yellow22
12blue12
13yellow22
14red123

<tbody>
</tbody>

Spreadsheet Formulas
CellFormula
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
 
Last edited:
Upvote 0
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
DE
1ColorFrequency
2blue3
3red2
4yellow3
Sheet2
 
Last edited by a moderator:
Upvote 0
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


DE
1ColorFrequency
2blue3
3red2
4yellow3

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:70.4px;"><col style="width:70.4px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
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 :)
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top