Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: sumif comparing columns ???

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I need to compare one column of numerical data on one worksheet with another column in a different worksheet. Then, where the numbers are the same I need the corresponding numbers from a third column totalling.
    Eg
    Sheet1..........Sheet2
    Col A...........Col A.....Col B
    (If the contents of the range of cells in column A of Sheet 1 equal the contents of the range of cells in column A of Sheet 2 - then sum all the corresponding cells in column B of sheet 2)
    Any ideas would be greatly appreciated.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    North Alabama, USA
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Not quite enough information, but sounds like a CSE or array formula so check out http://www.cpearson.com/excel/array.htm

    HTH
    Rocky...

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-27 07:47, rollo wrote:
    I need to compare one column of numerical data on one worksheet with another column in a different worksheet. Then, where the numbers are the same I need the corresponding numbers from a third column totalling.
    Eg
    Sheet1..........Sheet2
    Col A...........Col A.....Col B
    (If the contents of the range of cells in column A of Sheet 1 equal the contents of the range of cells in column A of Sheet 2 - then sum all the corresponding cells in column B of sheet 2)
    Any ideas would be greatly appreciated.
    Try in Sheet2:

    =SUMPRODUCT((Sheet1!A2:A40=A2:A40),B2:B40)


  4. #4
    New Member
    Join Date
    Feb 2002
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the post, Rocky E.
    Have looked at the link but, while =SUM(IF(A1:A10=B1:B10,1,0)) looks a possibility it only returns the number of cells in one range which are equal to their counterparts in the other range. I need a sum of the actual corresponding values from another column.

  5. #5
    New Member
    Join Date
    Feb 2002
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Oops sorry Aladin - just spotted your post. Will check it out. Many thanks.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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