# Count Unique Values from 3 Worksheets - HELP PLEASE!

#### mryexcel4

##### New Member
Hello,

I have 3 worksheets (Sheet 1, Sheet 2, Sheet 3) which contain email addresses in column J.
Each worksheet has a separate group/bucket of data however some of the email addresses in each sheet may be the same

I am looking for a formula to count only unique values (emails) from Sheet 1 column J which are not in Sheet 2 column J and are not in Sheet 3 column J.
For example, if johndoe@ is in Sheet 1 column J multiple times as well as in Sheet 2 and Sheet 3 column J multiple times. I would like to receive a count of 1.

Any suggestions would be highly appreciated.

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### XOR LX

##### Well-known Member
Hi.

It's not clear what you mean, unfortunately.

You say you want to count "only unique values (emails) from Sheet 1 column J which are not in Sheet 2 column J and are not in Sheet 3 column J".

But does that mean, for example, that an entry in Sheet 2 which does not occur in Sheet 1 or Sheet 3 is not to form part of the count, even though it is unique (as in it only occurs once across all 3 sheets)?

Perhaps you could post a small dataset with your expected results to clarify.

Regards

#### mryexcel4

##### New Member
Sorry for being unclear.

What I really need to accomplish is the following:
1. If an email is in more than one sheet only count the unique emails that are in one sheet and not the other 2. I would also like to use a condition if a record has a value of 1 for example in a cell, select only the unique emails that meet the condition.

Overall I need three formulas which each factor in the type of condition described above:
1. Select unique emails that are in Sheet 1 and not the other 2
2. Select unique emails that are in Sheet 2 and not the other 2
3. Select unique emails that are in Sheet 3 and not the other 2

Hope this helps.

QUOTE=XOR LX;3926902]Hi.

It's not clear what you mean, unfortunately.

You say you want to count "only unique values (emails) from Sheet 1 column J which are not in Sheet 2 column J and are not in Sheet 3 column J".

But does that mean, for example, that an entry in Sheet 2 which does not occur in Sheet 1 or Sheet 3 is not to form part of the count, even though it is unique (as in it only occurs once across all 3 sheets)?

Perhaps you could post a small dataset with your expected results to clarify.

Regards
[/QUOTE]

#### XOR LX

##### Well-known Member
Ok, but exactly how many counts are we talking about? 1? 3?

And what do these three new conditions that you've laid out mean? What does "select" here mean?

I think that you really should consider posting a small dataset to help clarify things. You could have three column headers - Sheet1, Sheet2, Sheet 3 - with some values underneath each (representing the column J values), together with your expected results.

Regards

#### mryexcel4

##### New Member
 Sheet 1 Email Sheet 1 Condition Sheet 2 Email Sheet 2 Condition Sheet 3 Email Sheet 3 Condition email1 1 email1 1 email2 1 email1 1 email4 1 email2 1 email4 1 email1 1 email3 1

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>
Sheet 1 count result after counting only unique values in sheet 1 that are not in the other 2 sheets with a condition value of 1 = 1 (email2)
Sheet 2 count result after counting only unique values in sheet 2 that are not in the other 2 sheets with a condition value of 1 = 0
Sheet 3 count result after counting only unique values in sheet 3 that are not in the other 2 sheets with a condition value of 1 = 1 (email4)

I have three columns which I need to enter the count results into and that is why I said I need 3 formulas (or one formula which I can edit for the other sheets). One for Sheet 1, another for Sheet 2, and a third for Sheet 3 results.

#### mryexcel4

##### New Member
My mistake. For "Sheet 1 count result after counting only unique values in sheet 1 that are not in the other 2 sheets with a condition value of 1 = 1 (email2)." The result would actually be email 3

#### XOR LX

##### Well-known Member
Forgive me but this is becoming, if anything, less clear, rather than more.

In the data you've posted, every entry has a Condition of 1, so why is this a factor? If it is in fact possible to have a Condition other than 1, wouldn't it have made sense to include some examples where that was the case?

Because of this, your statement "after counting only unique values in sheet 1 that are not in the other 2 sheets with a condition value of 1" is left ambiguous. To the value in which sheet(s) is the "condition value of 1" bit applying here?

What would your result be for Sheet 1 if the one entry of email3 had a Condition different from 1?

Also, where are your results to be stored? In a fourth sheet? Or do the results for Sheet 1 go in Sheet 1, the results for Sheet 2 in Sheet 2, etc.?

Regards

#### mryexcel4

##### New Member
Consider 1 to be 2014 and 2 to be 2013.

Regarding your question "What would your result be for Sheet 1 if the one entry of email3 had a Condition different from 1?"...
If email3 had a "condition" of 2 or 2013 then the result would be 0 if the formula condition required the "Sheet 1 Condition" column as well as the "Sheet 2 Condition" and "Sheet 3 Condition" columns to have a 1 for the value.
If the formula condition required the value to be 2 then the result would be 1.

The results will go into a separate worksheet into columns containing one cell each. One cell in one column for Sheet 1 count, another cell in the second column for Sheet 2 count, and another cell in the third column for Sheet 3 count.

Forgive me but this is becoming, if anything, less clear, rather than more.

In the data you've posted, every entry has a Condition of 1, so why is this a factor? If it is in fact possible to have a Condition other than 1, wouldn't it have made sense to include some examples where that was the case?

Because of this, your statement "after counting only unique values in sheet 1 that are not in the other 2 sheets with a condition value of 1" is left ambiguous. To the value in which sheet(s) is the "condition value of 1" bit applying here?

What would your result be for Sheet 1 if the one entry of email3 had a Condition different from 1?

Also, where are your results to be stored? In a fourth sheet? Or do the results for Sheet 1 go in Sheet 1, the results for Sheet 2 in Sheet 2, etc.?

Regards

#### XOR LX

##### Well-known Member
Apologies. It's not often that I'm completely bamboozled by a thread, but, 8 posts in, I still have absolutely no idea what you're asking for. Each post that you make seems to either feature some new, previously-unmentioned information or else contradict something you've said in an earlier post.

I hope that someone else will pick up on this thread shortly and that they will better understand your query.

Regards

#### mryexcel4

##### New Member
Okay.

What I did was ran three queries in sql server to obtain data. One query was to pull records including emails that were received 0-30 days after a certain date say 1/1/2014. The second query pulled results for 31-60 days and the third query pulled emails from 61-90 days. Each query results were then put into a separate Excel sheet. I want to count unique emails for sheet 1 that are not in sheet 2 or 3. Then do the same for sheet 2 and sheet 3. Then have the results entered into the separate cells and columns as I described earlier.

QUOTE=XOR LX;3927018]Apologies. It's not often that I'm completely bamboozled by a thread, but, 8 posts in, I still have absolutely no idea what you're asking for. Each post that you make seems to either feature some new, previously-unmentioned information or else contradict something you've said in an earlier post.

I hope that someone else will pick up on this thread shortly and that they will better understand your query.

Regards[/QUOTE]

Replies
7
Views
1K
Replies
0
Views
407
Replies
7
Views
178
Replies
1
Views
623
Replies
4
Views
162

1,191,049
Messages
5,984,358
Members
439,882
Latest member
gerdc

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

### Which adblocker are you using?

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

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