Compare 3 sets of data and display percentage of new entries

[Vadim Pavlovich]

Hello, 

we are running 3 surveys and we would like to compare how many new participants we are getting each time. We have 3 sets of email addresses from each survey.

We have following sets of data (~2000 emails each set)

Set 1:


1@email.com
2@email.com
3@email.com
4@email.com

Set 2:

1@email.com
2@email.com
5@email.com
6@email.com

Set 3:

1@email.com
2@email.com
5@email.com
8@email.com

Result we would like to get:

In data set 2 we have 50% new participants comparing with set 1
In data set 3 we have 50% new participants comparing with set 1 and 25% new participants comparing with set 2

I will really appreciate your help on that,
Thanks!

[#2]

Hi Vadim,

Let say your sets are in columns A, B and C. To calculate number of records in each set you may as

=COUNTA($A$1:$A$2000)

and how many records from set 1 are in set 2 as

=SUM(COUNTIF($A$1:$A$2000,$B$1:$B$2000))

above is array formula (Ctrl+Shift+Enter)

The rest is simple arithmetic.

You may use above formulas for entire columns, like

=SUM(COUNTIF($A:$A,$B:$B))

but it will take minutes to recalculate

[#3]

Hello Sergei,

For some reason I'm getting "0" as a result of this formula

=SUM(COUNTIF($B$1:$B$2000,$D$1:$D$2000))

In my case data set 1 is in range B:B, and data set 2 is in range D:D 

[#4]

Vadim, it looks like this and attached

Be sure you enter the formula as array one, i.e. by combination of Ctrl+Shift+Enter

[#5]

Thank you very much, I dot that working!

[#6]

You are welcome