# Help with count unique values in a column in Excel with vlookup

#### thepianoman12

##### New Member
Hi Everyone,

I would like to know if there is a function that can help me count unique values in spreadsheet X , in specific column (B) that match to specific values in sheet Y .
For example:

Sheet X:
A B
 01/09/2014 02/09/2014 03/09/2014 04/09/2014 05/09/2014

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

Sheet Y:

A B C D
 03/09/2014 00:00 63036777 Jack United States 03/09/2014 00:00 63023794 Jack United States 06/09/2014 00:00 63134683 Jack United States 06/09/2014 00:00 63133711 Jack United States 05/09/2014 00:00 63109595 Jack United States 05/09/2014 00:00 63109595 Dona United States 05/09/2014 00:00 63107710 Kim United States 05/09/2014 00:00 63091487 Dona Japan 05/09/2014 00:00 63091487 Jack Japan 05/09/2014 00:00 63084994 Dona Japan 05/09/2014 00:00 63084994 Kim Japan 05/09/2014 00:00 63076234 Kim United States 04/09/2014 00:00 63065845 Dona United States 04/09/2014 00:00 63065845 Jack United States 04/09/2014 00:00 63048586 Dona Japan 04/09/2014 00:00 63048586 Kim Japan

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>

I need that the function will count me how many unique values i had in sheet Y on column B that match the following conditions: from Japan (column D) and they match the date 04/09/2014 from column A in sheet X .

In This example the answer is 1.

Please Tell me if you need any additional details.

Thank you all for your help!

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Welcome to MrExcel.

Unique in what way? I would use a pivot table.

with an pivot table.

Book1
IJ
2RijlabelsAantal van country
33-9-2014 00:002
4Jack2
5630237941
6630367771
74-9-2014 00:004
8Dona2
9630485861
10630658451
11Jack1
12630658451
13Kim1
14630485861
155-9-2014 00:008
16Dona3
17630849941
18630914871
19631095951
20Jack2
21630914871
22631095951
23Kim3
24630762341
25630849941
26631077101
276-9-2014 00:002
28Jack2
29631337111
30631346831
31Eindtotaal16
Blad1

...
I need that the function will count me how many unique values i had in sheet Y on column B that match the following conditions: from Japan (column D) and they match the date 04/09/2014 from column A in sheet X .

In This example the answer is 1.
...

Sheet Y, A2:D17, houses a data sample.

Sheet X, A5 houses a date like 04/09/2014.

Let B1 house Japan as the country condition you mention.

B5, control+shift+enter, not just enter:
Rich (BB code):
``````=SUM(IF(FREQUENCY(IF(INT(Y!\$A\$2:\$A\$17)=\$A5,IF(Y!\$B\$2:\$B\$17<>"",
IF(Y!\$D\$2:\$D\$17=B\$1,MATCH(Y!\$B\$2:\$B\$17,Y!\$B\$2:\$B\$17,0)))),
ROW(Y!\$B\$2:\$B\$17)-ROW(Y!\$B\$2)+1),1))
``````

Is this what you are after?

Replies
5
Views
604
Replies
4
Views
591
Replies
2
Views
631
Replies
2
Views
439
Replies
9
Views
761

Threads
1,214,275
Messages
6,118,623
Members
448,844
Latest member
Jayee04e

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

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