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

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!

 

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