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

[Wayne Duncan]

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

 

[Aladin Akyurek]

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

 

[IML]

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)

 

[Andrew Poulsom]

Try this:

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

 

[Wayne Duncan]

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

 

[lecxe1]

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"

 

[Aladin Akyurek]

Re: 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"

Are you looking for?...

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

 

[lecxe1]

Re: Compare letter in two cells

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

 

[Aladin Akyurek]

Re: Compare letter in two cells

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.

 

[lecxe1]

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"<!-- / message -->