Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Brain dead today. Column A1:A10 contains numbers. Column B1:B10 contains text (one word per cell). On a different worksheet, I have the words "red", "blue" and "green" in A1:A3. Column A1:A10 on worksheet 2 contains words as well, including the three listed. What I would like to do is in column B on worksheet 2 sum the numbers in column A on worksheet 1 but only where the words red, blue and green are present on worksheet 2. Any ideas? Thaks in advance. I tried SUMIF(Worksheet1 B1:B10,OR("red","blue","green"),Worksheet1 A1:A10) but it didn't do it!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Can you try again? I'm getting confused over what is where - A1:A10 seems to contain numbers in one sentence, and words in the next...


  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    It would help if you posted a small sample workbook that gives us a clear and unambiguous idea of what you have and want.


  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Here you go.

    Attached Files Attached Files

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by jlkirk View Post
    Here you go.
    Never mind, figured it out: SUM(SUMIF(Sheet1!B1:B10,{"red","blue","green"},She et1!A1:A10))

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Quote Originally Posted by jlkirk View Post
    Never mind, figured it out: SUM(SUMIF(Sheet1!B1:B10,{"red","blue","green"},She et1!A1:A10))
    Or as an array formula (confirm with Ctrl+Shift+Enter):

    =SUM(IF(Sheet1!B1:B10={"red","blue","green"},Sheet 1!A1:A10))

  7. #7
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post
    Or this non-array formula :

    =SUMPRODUCT((Sheet1!B1:B10={"red","blue","green"}) *Sheet1!A1:A10)

    Regards
    Bosco

Posting Permissions

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