Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers 2

up vote 4 down vote accepted

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.

share|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
add comment

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

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

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)

share|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
    
Actually it should be, I don't know why it's not appearing in there. :D –  Alenanno Feb 15 '13 at 17:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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