# Thread: Compare row with column

1. I wish Excel allowed a straight-forward comparison of what I consider two vectors of data regardless of row or column orientation.

I have a row, say A1:E1 that I want to compare (element by element) to G1:G5

and, sum the equality. =sum(A1:E1=G1:G5) doesn't work.

Is there a simple solution to this that I'm overlooking?

2. When you say compare, what do you mean? Find the greater amount? Find the difference?

3. Originally Posted by WebGenii
When you say compare, what do you mean? Find the greater amount? Find the difference?
For example: if A1:E1 contains the solutions to multiple choice questions (e.g., a a c c b) and
G1:G5 contains the responses (e.g., c c c c c),

I want 2 as the answer. The number of correct responses (the third and fourth answers matched the correct answers)

To me, at least, I think this is a vector comparison using equals.
But, I think Excel believes that if the list goes down a column, it's treated like a 1 column matrix. SIGH.

4. If you were to place your comparison column as a row you could use the Array formula of:
=SUM(IF(A1:E1=A2:E2,1,0)) - confirmed with Ctrl+Shift+Enter

When comparing with a row a column, The number of matches is multiplied by the number of times the value appears in the column. Using the array formula of:
=SUM(IF(A1:E1=G1:G5,1,0)) results in 10 since the two C's in the Row will match 5 times in the column. Each value in the row gets compared with each value in the column.

5. I just remembered, you can use the TRANSPOSE function;

=SUM(IF(A1:E1=TRANSPOSE(G1:G5),1,0)) <-this too is an array formula -Ctrl+Shift+Enter to confirm

6. Nice catch!

7. Ah, yes...transpose because Excel things this is a one column matrix... Maybe the Microsoft boys don't know about vectors LOL.

THANKS!!!

Originally Posted by mbarron
I just remembered, you can use the TRANSPOSE function;

=SUM(IF(A1:E1=TRANSPOSE(G1:G5),1,0)) <-this too is an array formula -Ctrl+Shift+Enter to confirm

8. You could also use SUMPRODUCT which only involves 1 Function

e.g.

IF the vectors are in different directions use TRANSPOSE
and since TRANSPOSE is an Array Function you must use SHIFT CTRL ENTER

=SUMPRODUCT((A1:E1=TRANSPOSE(G1:G5))*1)

Otherwise if vectors are in the same direction you can just use as a normal NON Array Function

=SUMPRODUCT((A1:E1=A2:E2)*1)

=SUMPRODUCT((A1:A5=F11:F15)*1)

Note with SUMPRODUCT you must enclose the Comparison Expression in Brackets

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•