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

thepianoman12

New Member
Joined
Sep 7, 2014
Messages
1
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:0063036777JackUnited States
03/09/2014 00:0063023794JackUnited States
06/09/2014 00:0063134683JackUnited States
06/09/2014 00:0063133711JackUnited States
05/09/2014 00:0063109595JackUnited States
05/09/2014 00:0063109595DonaUnited States
05/09/2014 00:0063107710KimUnited States
05/09/2014 00:0063091487DonaJapan
05/09/2014 00:0063091487JackJapan
05/09/2014 00:0063084994DonaJapan
05/09/2014 00:0063084994KimJapan
05/09/2014 00:0063076234KimUnited States
04/09/2014 00:0063065845DonaUnited States
04/09/2014 00:0063065845JackUnited States
04/09/2014 00:0063048586DonaJapan
04/09/2014 00:0063048586KimJapan

<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

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Oeldere

Well-known Member
Joined
Dec 29, 2011
Messages
2,213
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
...
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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,874
Messages
5,834,170
Members
430,262
Latest member
JWDeiley

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

Which adblocker are you using?

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
Top