Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Sum if Cell contains 20L (Excel 2002)

    Hi

    I need to do the following.

    Please see attached, If the text in column "B" contains the text "20L" sum the equivalent quanties in column "C"

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sum if Cell contains 20L (Excel 2002)

    Try this:

    =SUMIF(B2:B26,"*20L*",C2:C26)

    The first argument is the range to test.
    The second argument is the condition. The * characters are wildcards: *20L* means 20L preceded by and followed by any number of characters.
    The third argument is the range to sum.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sum if Cell contains 20L (Excel 2002)

    How about the array (confirm with ctrl-shift-enter)
    <pre>=SUM(IF(ISNUMBER(SEARCH("20L",B2:B26)),C2:C26 ))</pre>


    Steve

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Sum if Cell contains 20L (Excel 2002)

    Hi Hans

    Thanks to You and Steve for the prompt reply.

    Both examples are adequate for my needs.

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  5. #5
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum if Cell contains 20L (Excel 2002)

    I know which solution I prefer **grin** !

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sum if Cell contains 20L (Excel 2002)

    I prefer Hans' solution and i would have suggested it if i had thought of it... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Steve

  7. #7
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum if Cell contains 20L (Excel 2002)

    Although neither works if the objective is to detect the upper case L in the string, as opposed to lower case l.
    But I'm sure Braddy can sort out something elaborate to distinguish these, possibly using the CODE function to look for 76 (for L) rather than 108 (for l).
    Keep smiling!

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sum if Cell contains 20L (Excel 2002)

    The array I suugest can distinguish the difference if find is used instead of search...

    Steve

Posting Permissions

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