# Thread: Sum if Cell contains 20L (Excel 2002)

1. ## 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

2. ## 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. ## 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. ## 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

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

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

6. ## 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. ## 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. ## 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
•