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

[thepianoman12]

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
[TABLE="width: 394"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD="align: right"]01/09/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]02/09/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03/09/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]04/09/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]05/09/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet Y:

A B C D
[TABLE="width: 510"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD="align: right"]03/09/2014 00:00[/TD]
[TD="align: right"]63036777[/TD]
[TD][/TD]
[TD]Jack[/TD]
[TD]United States[/TD]
[/TR]
[TR]
[TD="align: right"]03/09/2014 00:00[/TD]
[TD="align: right"]63023794[/TD]
[TD][/TD]
[TD]Jack[/TD]
[TD]United States[/TD]
[/TR]
[TR]
[TD="align: right"]06/09/2014 00:00[/TD]
[TD="align: right"]63134683[/TD]
[TD][/TD]
[TD]Jack[/TD]
[TD]United States[/TD]
[/TR]
[TR]
[TD="align: right"]06/09/2014 00:00[/TD]
[TD="align: right"]63133711[/TD]
[TD][/TD]
[TD]Jack[/TD]
[TD]United States[/TD]
[/TR]
[TR]
[TD="align: right"]05/09/2014 00:00[/TD]
[TD="align: right"]63109595[/TD]
[TD][/TD]
[TD]Jack[/TD]
[TD]United States[/TD]
[/TR]
[TR]
[TD="align: right"]05/09/2014 00:00[/TD]
[TD="align: right"]63109595[/TD]
[TD][/TD]
[TD]Dona[/TD]
[TD]United States[/TD]
[/TR]
[TR]
[TD="align: right"]05/09/2014 00:00[/TD]
[TD="align: right"]63107710[/TD]
[TD][/TD]
[TD]Kim[/TD]
[TD]United States[/TD]
[/TR]
[TR]
[TD="align: right"]05/09/2014 00:00[/TD]
[TD="align: right"]63091487[/TD]
[TD][/TD]
[TD]Dona[/TD]
[TD]Japan[/TD]
[/TR]
[TR]
[TD="align: right"]05/09/2014 00:00[/TD]
[TD="align: right"]63091487[/TD]
[TD][/TD]
[TD]Jack[/TD]
[TD]Japan[/TD]
[/TR]
[TR]
[TD="align: right"]05/09/2014 00:00[/TD]
[TD="align: right"]63084994[/TD]
[TD][/TD]
[TD]Dona[/TD]
[TD]Japan[/TD]
[/TR]
[TR]
[TD="align: right"]05/09/2014 00:00[/TD]
[TD="align: right"]63084994[/TD]
[TD][/TD]
[TD]Kim[/TD]
[TD]Japan[/TD]
[/TR]
[TR]
[TD="align: right"]05/09/2014 00:00[/TD]
[TD="align: right"]63076234[/TD]
[TD][/TD]
[TD]Kim[/TD]
[TD]United States[/TD]
[/TR]
[TR]
[TD="align: right"]04/09/2014 00:00[/TD]
[TD="align: right"]63065845[/TD]
[TD][/TD]
[TD]Dona[/TD]
[TD]United States[/TD]
[/TR]
[TR]
[TD="align: right"]04/09/2014 00:00[/TD]
[TD="align: right"]63065845[/TD]
[TD][/TD]
[TD]Jack[/TD]
[TD]United States[/TD]
[/TR]
[TR]
[TD="align: right"]04/09/2014 00:00[/TD]
[TD="align: right"]63048586[/TD]
[TD][/TD]
[TD]Dona[/TD]
[TD]Japan[/TD]
[/TR]
[TR]
[TD="align: right"]04/09/2014 00:00[/TD]
[TD="align: right"]63048586[/TD]
[TD][/TD]
[TD]Kim[/TD]
[TD]Japan[/TD]
[/TR]
</tbody>[/TABLE]


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!

 

[Andrew Poulsom]

Welcome to MrExcel.

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

 

[Oeldere]

with an pivot table.

Book1
	I	J
2	Rijlabels	Aantal van country
3	3-9-2014 00:00	2
4	Jack	2
5	63023794	1
6	63036777	1
7	4-9-2014 00:00	4
8	Dona	2
9	63048586	1
10	63065845	1
11	Jack	1
12	63065845	1
13	Kim	1
14	63048586	1
15	5-9-2014 00:00	8
16	Dona	3
17	63084994	1
18	63091487	1
19	63109595	1
20	Jack	2
21	63091487	1
22	63109595	1
23	Kim	3
24	63076234	1
25	63084994	1
26	63107710	1
27	6-9-2014 00:00	2
28	Jack	2
29	63133711	1
30	63134683	1
31	Eindtotaal	16
Blad1

 

[Aladin Akyurek]

...
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:

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