Function to count unique values where condition is

A

Anban

New Member
Joined
Aug 4, 2014
Messages
6
Hi there,

im new here. i found here many aswers but now i have problem where i cant find way out :/

I have data:

[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64"]Day[/TD]
[TD="width: 64"]ID (Number)[/TD]
[/TR]
[TR]
[TD]Mon.[/TD]
[TD="class: xl65, align: right"]111[/TD]
[/TR]
[TR]
[TD]Mon.[/TD]
[TD="class: xl65, align: right"]111[/TD]
[/TR]
[TR]
[TD]Mon.[/TD]
[TD="class: xl65, align: right"]222[/TD]
[/TR]
[TR]
[TD]Mon.[/TD]
[TD="class: xl65, align: right"]333[/TD]
[/TR]
[TR]
[TD]Mon.[/TD]
[TD="class: xl65, align: right"]111[/TD]
[/TR]
[TR]
[TD]Tue.[/TD]
[TD="class: xl65, align: right"]222[/TD]
[/TR]
[TR]
[TD]Tue.[/TD]
[TD="class: xl65, align: right"]111[/TD]
[/TR]
[TR]
[TD]Tue.[/TD]
[TD="class: xl65, align: right"]555[/TD]
[/TR]
[TR]
[TD]Tue.[/TD]
[TD="class: xl65, align: right"]666[/TD]
[/TR]
[TR]
[TD]Tue.[/TD]
[TD="class: xl65, align: right"]777[/TD]
[/TR]
[TR]
[TD]Tue.[/TD]
[TD="class: xl65, align: right"]333[/TD]
[/TR]
[TR]
[TD]Tue.[/TD]
[TD="class: xl65, align: right"]222[/TD]
[/TR]
[TR]
[TD]Tue.[/TD]
[TD="class: xl65, align: right"]999[/TD]
[/TR]
[TR]
[TD]Tue.[/TD]
[TD="class: xl65, align: right"]888[/TD]
[/TR]
[TR]
[TD]Tue.[/TD]
[TD="class: xl65, align: right"]888
[/TD]
[/TR]
</tbody>[/TABLE]
I would like to count how many unique values we have i typed function: =SUM(IF(FREQUENCY(B3:B17;B3:B17)>0;1)) and it return value 8. What is OK.

Now i would like to know, how many unique value we have in each day:

I have to get:

Mon. 3
Tue. 5

I've tried a lot of functions; combinations with IF statments, And statments, but i cant find right solution :(

I can get that numbers to use filters and then for each day use forumla above, but its very boring because i have 103 different days.

Tnx for help and stay cool.

N.
 
Sort by date Sort by votes
E3, control+shift+enter, not just enter, and copy down:
Rich (BB code): 
=SUM(IF(FREQUENCY(IF(A3:A17=D3,B3:B17),B3:B17)>0,1))

where D3 houses a criterion value like Mon followed by a dot.
 
Upvote 0
Aladin, tnx for fast reply

I tried this before i post question and it doesnt work for me...it returns me again 8.

probbably its problem with ctrl+shift+enter, because i dont understand where is problem if i just copied formula. (i am amateur). probbaly i will need more detailed instructions.
 
Upvote 0
Anban said:
Aladin, tnx for fast reply

I tried this before i post question and it doesnt work for me...it returns me again 8.

probbably its problem with ctrl+shift+enter, because i dont understand where is problem if i just copied formula. (i am amateur). probbaly i will need more detailed instructions.
Click to expand...

It works! But only for Mon.= returns 3

When i try with Tue. it returns me again 8. I typed forumla and then press control+shift+ enter and returns me 8. Why is that?
 
Last edited:
Upvote 0
I'm not really following. Tue. contains 8 unique values and Mon. contains 3. the total number of unique values between the 2 days would be 8.

are you not wanting to include the unique values from the day before? so if 111 222 333 where counted on Monday then don't count them on Tuesday?
 
Upvote 0
Anban said:
It works! But only for Mon.= returns 3

When i try with Tue. it returns me again 8. I typed forumla and then press control+shift+ enter and returns me 8. Why is that?
Click to expand...

I missed the additional condition the first round...


[TABLE="width: 240"]
<TBODY>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"][/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]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]#Day#
[/TD]
[TD="class: xl63, bgcolor: transparent"]Count
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]Mon.
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]111
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]Mon.
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]Mon.
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]111
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]Tue.
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]5
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]Mon.
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]222
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]Mon.
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]333
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]Mon.
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]111
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]Tue.
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]222
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]Tue.
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]111
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]Tue.
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]555
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]Tue.
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]666
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]Tue.
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]777
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]Tue.
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]333
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]Tue.
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]222
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]Tue.
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]999
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]Tue.
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]888
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]Tue.
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]888
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

F3, control+shift+enter, not just enter, and copy down:
Rich (BB code): 

=SUM(IF(FREQUENCY(IF($A$3:$A$17=D3,IF(1-ISNUMBER(MATCH($B$3:$B$17,
  IF(ISNUMBER(MATCH($A$3:$A$17,$D$2:D2,0)),$B$3:$B$17),0)),
  MATCH($B$3:$B$17,$B$3:$B$17,0))),ROW($B$3:$B$17)-ROW($B$3)+1),1))
 
Upvote 0
Aladin Akyurek said:
I missed the additional condition the first round...


[TABLE="width: 240"]
<tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"][/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]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]#Day#[/TD]
[TD="class: xl63, bgcolor: transparent"]Count[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]Mon.[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]111[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]Mon.[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]Mon.[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]111[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]Tue.[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]Mon.[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]222[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]Mon.[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]333[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]Mon.[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]111[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]Tue.[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]222[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]Tue.[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]111[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]Tue.[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]555[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]Tue.[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]666[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]Tue.[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]777[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]Tue.[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]333[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]Tue.[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]222[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]Tue.[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]999[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]Tue.[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]888[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]Tue.[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]888[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

F3, control+shift+enter, not just enter, and copy down:
Rich (BB code): 

=SUM(IF(FREQUENCY(IF($A$3:$A$17=D3,IF(1-ISNUMBER(MATCH($B$3:$B$17,
  IF(ISNUMBER(MATCH($A$3:$A$17,$D$2:D2,0)),$B$3:$B$17),0)),
  MATCH($B$3:$B$17,$B$3:$B$17,0))),ROW($B$3:$B$17)-ROW($B$3)+1),1))
Click to expand...


Now it really works, tnx 100x times :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
Count of unique sub-entries
Replies
3
Views
305
dreid1011
dreid1011
B
  • Question Question
Excel VBA count unique values in a column within a named range
Replies
6
Views
267
Peter_SSs
Peter_SSs
K
  • Solved
Count number of max values per unique value and column heading?
Replies
5
Views
362
RobP
R
M
  • Solved
Count Unique Values Based on Unique (sort of) Criteria
Replies
4
Views
473
Fluff
Fluff
G
  • Question Question
INDEX Functions with multiple criteria
Replies
5
Views
486
Phuoc
P

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