Sum, Countif, and Array nested function help

[lea334]

Hello -

I have two columns

First column has:

a
b
c
d
e
f

Second column has:

a
b
z
z
z
z

Each column has 6 values. I'm trying to find a formula that will count the number of values that DO NOT overlap between the two.

I used =SUM(COUNTIF(C4:C9, E4:E9)) as an array to count the number of values that do overlap, which is 2.

But what is the formula for me to get 4, the number of values that don't overlap? I tried =SUM(COUNTIF(C4:C9, "<>E4:E9")) as an array but that didn't work.

Any help would be appreciated. Thanks!

[mehmetcik]

Please Login or Register  to view this content.

[lea334]

Thanks. I think that only works if I write the number, such as 6. Is there no way to automate that? I won't be able to apply this formula to larger files where I won't be counting the number of rows...just simply want to know how many don't overlap.

Does that make sense? I guess I don't understand why the syntax for not overlapping values is not similar to/as easy as for overlapping values.

[AlKey]

Maybe this

=COUNTA(C:E)/2-SUMPRODUCT(--(C4:C9=E4:E9))

[lea334]

Wow, that works great! Thank you!

Can you briefly explain the logic behind that please? You're counting all the values in those two columns then subtracting the values that equal each other in the columns?

[XOR LX]

Hi.

When you say "overlap", what precisely do you mean?

In your example, all 4 values which contribute to your desired result come from the same column. Is that how the problem should be defined, then?

What if you had values such as:

C4:C9: a, a, a, a, a, b
E4:E9: a, c, d, e, f, g

i.e 7 different values used in total, and AlKey's formula would here return a result of 5.

However, if you are considering from the perspective of both columns' entries, then you could argue that the result should be 6, not 5, since b, c, d, e, f and g do not "overlap" into the other column.

Regards

[lea334]

Ok I understand your question.

I will be using this to compare a list of attendees from one year to the next.

For example, my first column will have names:

John
Alan
Brad
Chris
Kyle
Zack

My second column will have names for the following year:

John
Alan
Brad
Chris
Keith
Tom

I want the formula to tell me that two people from last year did not return to the conference. There will not be the same name multiple times so I think Alkey's formula will work.

Am I thinking about it right?

[XOR LX]

AlKey's formula will work, but only if the names of people who did attend in each of those years are placed within the same row of their respective columns, "facing each other", if you like.

They are in the two examples you've so far given, but, if you can't always guarantee that, then you should use, e.g.:

=COUNTA(C4:C9)-SUMPRODUCT(COUNTIF(E4:E9,C4:C9))

Regards

[lea334]

That's so great, thank you!

Thank you for clarifying the differences between the two formulas as well.

[XOR LX]

You're welcome!