Count unique value in range based on another range

[HaroonSid]

Hi,
I have a range sheet1 d10:d300

If i input a unique value in range sheet2 f10:f1000
Then count the unique value
For example
In sheet1 d10:d300 i have 1'2'3'7

I input some value
Sheet2 f10:f1000 = 12345679

Now count the unique value
Answer is = 4 (4'5'6'9)

[FlameRetired]

I am not clear concerning the relative layouts and cell contents in your description.

It would save time and clarify if you upload a sample Excel file.

Given you have over 1500 posts I'll assume you already know how to do this.

[HaroonSid]

Here is screenshot
1.JPG
2.JPG

[63falcondude]

Edit: Deleted

[FlameRetired]

Try array entering this formula in E4 of Sheet2. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

[FlameRetired]

Another way non array entered.

Please Login or Register  to view this content.

[HaroonSid]

HI,
thank you for the reply

i used your formula in this way in range O4

Please Login or Register  to view this content.

i have no value in range f10:f1000 and entry!d10:d300
but its showing in O4=991?

i need as a result o4 = 0

if i add unique value in range f10:f1000 which one is not in the list of ENTRY!D10:D300 then count that value in range O4


thank you

[HaroonSid]

I Think i was asking a wrong question about my need

i want to count of list f10:f1000 only value which one are not available in range ENTRY! D10:D300

[AliGW]

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[shukla.ankur281190]

Try

Please Login or Register  to view this content.

[HaroonSid]

sample workbook is here

[HaroonSid]

sorry again wrong question asked by me

actually i want to compare two list from different sheets
list1 and list2

count WHAT?

Count those values of list1 which are not in list2

list1 = 1,2,3,4,5

list2 = 1,2,3,4,5,6,7

now if we compare list1 and list2

in list two 6,7 is not in list1
and this is the answer

count = 2

thank you
hope you will understand

[shukla.ankur281190]

Try

H3

Please Login or Register  to view this content.

[HaroonSid]

Please ignore blank cells if i increase range of formula

Please Login or Register  to view this content.

this formula counting blank cells too

[shukla.ankur281190]

Try

H13

Please Login or Register  to view this content.

[HaroonSid]

Ek Dum Perfect

thank you very very much for your precious time

dear