# Function to count unique values where condition is

#### Anban

##### New Member
Hi there,

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

I have data:

 Day ID (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.

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

##### MrExcel MVP
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

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

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

##### MrExcel MVP
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
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))
``````

Now it really works, tnx 100x times

##### MrExcel MVP
Now it really works, tnx 100x times
You are welcome. Thanks for providing feedback.