Count Unique values based on a criteria in a separate column

J

jak26

Board Regular
Joined
Dec 4, 2007
Messages
53
Hi,

I have seen many like this but cannot get it to work.

I have Products in Column D and a concatenate of the year and week in Column O. IE

Column D Column O
A 20111
B 20111
A 20111
B 20111
C 20112

What I want is to count the number of unique values in Column D based on the value in Column O. For the example it should return '2'.

There are 15000 rows and column O has 104 different values ie 2 years

Thanks
Jeff
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
Hi jak26,

Assuming the products are in D2:D6, the week and year are in O2:O6 and column P is free (change these to suit), try these two steps:

1. In cell P2 enter the formula =D2&O2 and fill down to cell P6
2. In any blank cell enter the formula =SUMPRODUCT(($P$2:$P$6<>"")/COUNTIF($P$2:$P$6,$P$2:$P$6))

Note too that there are 3 unique entries ('A 20111', 'B 20111' and 'C20112') in your stated example.

HTH

Robert
 
Last edited:
Upvote 0
Hi,

Thanks for the response

However, I wish to count the unique entries for each year/week.

In the example,

20111 would return 2
20112 would return 1

Cheers
Jeff
 
Upvote 0
jak26 said:
Hi,

Thanks for the response

However, I wish to count the unique entries for each year/week.

In the example,

20111 would return 2
20112 would return 1

Cheers
Jeff
Click to expand...

<TABLE style="WIDTH: 240pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=320><COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2878064 class=xl63 height=19 width=64>Col D</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>Col O</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19>A
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>20111</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>20111</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>2</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>20111</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>20112</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>1</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>20111</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>20111</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19>C</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2878064 class=xl63 align=right>20112</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR></TBODY></TABLE>

R2, control+shift+enter, not just enter, and copy down:
Rich (BB code): 
=SUM(IF(FREQUENCY(IF($D$2:$D$6<>"",IF($O$2:$O$6=Q2,
   MATCH("~"&$D$2:$D$6,$D$2:$D$6&"",))),
     ROW($D$2:$D$6)-ROW($D$2)+1),1))

If no special meaning chars surround the entries in column D, the "~"& and &"" bits can be safely removed from the foregoing formula if so desired.
 
Upvote 0
jak26 said:
Hi,

Thanks for the response

However, I wish to count the unique entries for each year/week.

In the example,

20111 would return 2
20112 would return 1

Cheers
Jeff
Click to expand...
Try this...

Book1
DOPQR
2A20111_201112
3B20111_201121
4A20111___
5B20111___
6C20112___
Sheet1

This array formula** entered in R2 and copied down:

=SUM(IF(FREQUENCY(IF(O$2:O$6=Q2,MATCH(D$2:D$6,D$2:D$6,0)),ROW(D$2:D$6)-ROW(D$2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Assuming the values in column O are NUMERIC values.

a concatenate of the year and week in Column O.
Click to expand...
Is this being done by a formula? If so, what does that formula look like? It may be that these values are TEXT strings (even though they may look like numbers) and the values in column Q are NUMERIC numbers and this will lead to data type mismatching (read: the formula will not work!!!).
 
Upvote 0
Re: Count Unique values based on a criteria in a separate column <SOLVED>

Thanks people

Aladins formula worked great and I discovered just prior to valko's post that my concatenate was text and required a value() to fix

Cheers
 
Upvote 0
Re: Count Unique values based on a criteria in a separate column <SOLVED>

jak26 said:
Thanks people

Aladins formula worked great and I discovered just prior to valko's post that my concatenate was text and required a value() to fix

Cheers
Click to expand...

You are welcome. Thanks for providing feedback.

By the way, you can either introduce Q2&"" in the formula or tweak the concatenation as in =(A2&B2)+0.
 
Upvote 0
Re: Count Unique values based on a criteria in a separate column <SOLVED>

jak26 said:
Thanks people

I discovered just prior to valko's post that my concatenate was text and required a value() to fix

Cheers
Click to expand...
Good deal. Thanks for the feedback! :cool:
 
Upvote 0
Teşekkürler. Thanks..

Aladin Akyurek said:
[TABLE="width: 240"]
<colgroup><col style="WIDTH: 48pt" span="5" width="64"></colgroup><tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]Col D[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Col O[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]A
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]20111[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]20111[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]B[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]20111[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]20112[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]A[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]20111[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]B[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]20111[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]C[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]20112[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


R2, control+shift+enter, not just enter, and copy down:
Rich (BB code): 
=SUM(IF(FREQUENCY(IF($D$2:$D$6<>"",IF($O$2:$O$6=Q2,
   MATCH("~"&$D$2:$D$6,$D$2:$D$6&"",))),
     ROW($D$2:$D$6)-ROW($D$2)+1),1))

If no special meaning chars surround the entries in column D, the "~"& and &"" bits can be safely removed from the foregoing formula if so desired.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Count number of max values per unique value and column heading?
Replies
5
Views
288
RobP
R
A
  • Question Question
Dynamic data rollup by Row - non specific criteria
Replies
8
Views
348
Andy395
A
C
  • Solved
COUNTIFS
Replies
6
Views
229
carlclements
C
S
  • Question Question
Compressing large amount of rows but adding totals?
Replies
6
Views
130
jkpieterse
jkpieterse
A
  • Solved
Count unique IDs with a wildcard criteria for another column
Replies
2
Views
187
ausswe
A

Forum statistics

Threads
1,224,618
Messages
6,179,917
Members
452,949
Latest member
beartooth91

Share this page

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