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!
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Oeldere

Well-known Member
Joined
Dec 29, 2011
Messages
2,213
with an pivot table.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Rijlabels</td><td style=";">Aantal van country</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">3-9-2014 00:00</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Jack</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">63023794</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">63036777</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">4-9-2014 00:00</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Dona</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">63048586</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">63065845</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">Jack</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">63065845</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">Kim</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">63048586</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">5-9-2014 00:00</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">Dona</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">63084994</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style=";">63091487</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style=";">63109595</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style=";">Jack</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style=";">63091487</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style=";">63109595</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style=";">Kim</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style=";">63076234</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style=";">63084994</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style=";">63107710</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style=";">6-9-2014 00:00</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style=";">Jack</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style=";">63133711</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style=";">63134683</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style=";">Eindtotaal</td><td style="text-align: right;;">16</td></tr></tbody></table><p style="width:3em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Blad1</p><br /><br />
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
...
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,689
Messages
5,597,550
Members
414,155
Latest member
Grainne whiteside

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