Function to count unique values where condition is

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:

DayID (Number)
Mon.111
Mon.111
Mon.222
Mon.333
Mon.111
Tue.222
Tue.111
Tue.555
Tue.666
Tue.777
Tue.333
Tue.222
Tue.999
Tue.888
Tue.888

<tbody>
</tbody>
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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
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.
 

Anban

New Member
Joined
Aug 4, 2014
Messages
6
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.
 

Anban

New Member
Joined
Aug 4, 2014
Messages
6
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.
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:

Weazel

Well-known Member
Joined
Dec 24, 2011
Messages
3,155
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?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
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?
I missed the additional condition the first round...


#Day#
Count
Mon.
111
Mon.
3
Mon.
111
Tue.
5
Mon.
222
Mon.
333
Mon.
111
Tue.
222
Tue.
111
Tue.
555
Tue.
666
Tue.
777
Tue.
333
Tue.
222
Tue.
999
Tue.
888
Tue.
888

<TBODY>
</TBODY>

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))
 

Anban

New Member
Joined
Aug 4, 2014
Messages
6
I missed the additional condition the first round...


#Day#Count
Mon.111Mon.3
Mon.111Tue.5
Mon.222
Mon.333
Mon.111
Tue.222
Tue.111
Tue.555
Tue.666
Tue.777
Tue.333
Tue.222
Tue.999
Tue.888
Tue.888

<tbody>
</tbody>

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))

Now it really works, tnx 100x times :)
 

Forum statistics

Threads
1,082,243
Messages
5,363,972
Members
400,772
Latest member
solbebe

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top