Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    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. #2
    Super Moderator WebGenii's Avatar
    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

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    Quote Originally Posted by WebGenii View Post
    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. #4
    5 Star Lounger
    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.

  5. #5
    5 Star Lounger
    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

  6. #6
    Super Moderator WebGenii's Avatar
    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

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    Ah, yes...transpose because Excel things this is a one column matrix... Maybe the Microsoft boys don't know about vectors LOL.

    THANKS!!!

    Quote Originally Posted by mbarron View Post
    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. #8
    5 Star Lounger AndrewKKWalker's Avatar
    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 Brackets
    Andrew

Posting Permissions

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