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

Wayne Duncan

Board Regular
Joined
Apr 7, 2002
Messages
75
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
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)
 

Wayne Duncan

Board Regular
Joined
Apr 7, 2002
Messages
75

ADVERTISEMENT

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

lecxe1

New Member
Joined
Sep 5, 2009
Messages
3
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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

New Member
Joined
Sep 5, 2009
Messages
3
Re: Compare letter in two cells

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

New Member
Joined
Sep 5, 2009
Messages
3
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 -->
 

Forum statistics

Threads
1,144,312
Messages
5,723,651
Members
422,508
Latest member
Lordkit1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top