# find all cells in col A that start with a given 2 letters

This is a discussion on find all cells in col A that start with a given 2 letters within the Excel Questions forums, part of the Question Forums category; In col A I have various text codes in no particular order: i.e.cell A2 is PM-A01, cell A3 is BTC05, ...

1. In col A I have various text codes in no particular order: i.e.cell A2 is PM-A01, cell A3 is BTC05, cell A4 is PM-B00, etc. The first two positions are always alphabetic. I want to sum all the numbers in column B whose adjacent column A text starts with "PM".
I tried =IF(match("PM*",A2:A100,0),b2,"") but just get "NA"
Any suggestions?

[ This Message was edited by: Wayne Duncan on 2002-11-07 09:31 ]

2. On 2002-11-07 09:30, Wayne Duncan wrote:
In col A I have various text codes in no particular order: i.e.cell A2 is PM-A01, cell A3 is BTC05, cell A4 is PM-B00, etc. The first two positions are always alphabetic. I want to sum all the numbers in column B whose adjacent column A text starts with "PM".
I tried =IF(match("PM*",A2:A100,0),b2,"") but just get "NA"
Any suggestions?

[ This Message was edited by: Wayne Duncan on 2002-11-07 09:31 ]
=SUMPRODUCT((LEFT(A2:A100,2)="PM")+0,B2:B100)

& using SUMIF is much cheaper...

[ This Message was edited by: Aladin Akyurek on 2002-11-07 09:40 ]

3. You're going to kick yourself on this one. You want to sum if criteria are met?
Use Sumif

Syntax would be
=SUMIF(A1:A10,"PM*",B1:B10)

4. Try this:

=SUMIF(A1:A100,"PM*",B1:B100)

5. Thanks to all for your prompt responses. I am humbled.

6. ## Compare letter in two cells

Not very good with excel formulas - I'm looking for a "true/false" formula similar to (=EXACT) that will compare a letter in two cells.

For example:
Cell E5 contains "auto"
Cell M5 contains "a-full"

I'm looking for a formula that will only look at the letter 'a' in each cell and give me a result "TRUE"

7. ## Re: Compare letter in two cells

Originally Posted by lecxe1
Not very good with excel formulas - I'm looking for a "true/false" formula similar to (=EXACT) that will compare a letter in two cells.

For example:
Cell E5 contains "auto"
Cell M5 contains "a-full"

I'm looking for a formula that will only look at the letter 'a' in each cell and give me a result "TRUE"
Are you looking for?...

=EXACT(LEFT(E5,1),LEFT(M5,1))

8. ## Re: Compare letter in two cells

This work, thanks a million!
Can you explain the formula so I can learn the logic?

9. ## Re: Compare letter in two cells

Originally Posted by lecxe1
This work, thanks a million!
Can you explain the formula so I can learn the logic?
LEFT(E5,1) yields the first letter/char from the left of the string in E5.

LEFT(M5,1) yields the first letter/char from the left of the string in M5.

EXACT compares those two chars for being exactly the same with as result either TRUE or FALSE.

10. ## Re: Compare letter in two cells

Thanks! I have another one for you and I promise I won't bother you for the read of the day
Cell C5 contains "1/1"
Cell K5 contains "Gi1/1"

I'm looking for a formula that will compare the "1/1" in each cell (ignoring "Gi" in cell K5) and give me a result "TRUE"

Page 1 of 2 12 Last

#### Posting Permissions

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