7

I am working on a Google Doc spreadsheet using a not-so-simple formula. The formula works but is not as I want it. What I want to achieve is:

"Compare each cell from T3 to AM3 to each cell from T2 to AM2 and give 1 if they match, otherwise 0. Then make a sum of it all.

So if 10 pair of cells match, I should have 10. If 13 match, 13, and so on. If none match, 0.

I thought of writing something like

=SUM(IF(T3:AM3=$T$2:$AM$2;1;0))

But this gives me 1 if they all match and 0 even in the case of one pair not matching. How do I tell the spreadsheet to apply to every pair?

And how can I "sum"? The fact is "sum" won't work without at least two parameters and I don't need a second parameter. I'm at a dead end.

| improve this question | |
6

Try this:

=arrayformula(sumproduct((T2:AM2=T3:AM3)))

What this will do is compare each cell, return a 0 or 1 if true, and then sum up the counts.

Arrayformula() is a way to apply a function to an array, rather than a single cell.

| improve this answer | |
  • Thanks, this works like a charm. On a cell it gives me an unexplainable "8" even if the cells are empty. Weird, the others work perfectly... Anyway, your answer worked so I'm accepting it. If you have any idea about that 8, please let me know. If not, thanks for your help anyway! :D – Alenanno Feb 13 '13 at 22:55
  • Ah nevermind, I think I fixed it. In case others need it: I selected the cells that might have been affected and went to Format > Clear Formatting. Thanks again! – Alenanno Feb 13 '13 at 23:07
  • Glad it works for you @Alenanno! – OnenOnlyWalter Feb 13 '13 at 23:32
1
=Countif(t2;t3)

On the row below those you have and drag it to the latest last column you want (you can hide this row if you want later), then count that whole row.

| improve this answer | |
1

You where pretty close. As to the solution provided by @OnenOlyWalter (+1), this is how you could have resolved it as well.

Formula

=SUM(ARRAYFORMULA(IF(T2:AM2=T3:AM3;1;0)))

Explained

It follows exactly the way you told it:

"Compare each cell if they match (IF STATEMENT)
from T3 to AM3 to each cell from T2 to AM2 (ARRAYFORMULA)
Then make a sum of it all" (SUM STATEMENT)

Screenshot

enter image description here

Example

I've created an example file for you: compare each cell of two rows

| improve this answer | |
  • 1
    I knew the solution was the array! Thanks for the input. I have used Onen's solution but I'm going to upvote yours too for the extra help. :) – Alenanno Feb 14 '13 at 10:06
  • Jacob, by the way, by "I knew the solution was the array!" I mean that I was suspecting it but you confirmed it for me. Re-reading it now, it seems I'm saying your answer was already known to me which is not true lol :) – Alenanno Feb 15 '13 at 15:33
  • Thanks for letting me know and none taken. Good luck mastering the formulas !! – Jacob Jan Tuinstra Feb 15 '13 at 17:33
  • @Alenanno btw, shouldn't you be adding Dutch to the list? – Jacob Jan Tuinstra Feb 15 '13 at 17:36
  • 1
    Actually it should be, I don't know why it's not appearing in there. :D – Alenanno Feb 15 '13 at 17:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.