Results 1 to 8 of 8
Thread: Compare row with column

20100106, 16:13 #1
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,576
 Thanks
 44
 Thanked 73 Times in 69 Posts
I wish Excel allowed a straightforward 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?

20100106, 16:21 #2
 Join Date
 Jan 2001
 Location
 Redcliff, Alberta, Canada
 Posts
 4,066
 Thanks
 2
 Thanked 5 Times in 5 Posts
When you say compare, what do you mean? Find the greater amount? Find the difference?
[b]Catharine Richardson (WebGenii)
WebGenii Home Page
Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

20100106, 16:27 #3
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,576
 Thanks
 44
 Thanked 73 Times in 69 Posts
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.

20100106, 16:54 #4
 Join Date
 Aug 2004
 Location
 Connecticut, USA
 Posts
 816
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20100106, 16:58 #5
 Join Date
 Aug 2004
 Location
 Connecticut, USA
 Posts
 816
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20100106, 17:00 #6
 Join Date
 Jan 2001
 Location
 Redcliff, Alberta, Canada
 Posts
 4,066
 Thanks
 2
 Thanked 5 Times in 5 Posts
Nice catch!
[b]Catharine Richardson (WebGenii)
WebGenii Home Page
Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

20100106, 18:18 #7
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,576
 Thanks
 44
 Thanked 73 Times in 69 Posts

20100106, 19:22 #8
 Join Date
 Apr 2001
 Location
 Cambridge, UK
 Posts
 1,020
 Thanks
 0
 Thanked 3 Times in 3 Posts
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 BracketsAndrew