Formula for cells that contain a specific letter
 

I am trying to find a formula that will sum the numbers in a group of
cells that have a specific letter in the number: The letter will be
either "A" for one formula or "S" in the other.....

The numbers/letters in a cell look like 5S or 2A - It is for Annual or
Sick Leave on a timesheet

Formula for cells that contain a specific letter
 

If the number will on end with 1 letter, then try something like this:

For values in A1:A5

This formula adds the "numbers" that end in "S":
B1: =SUMPRODUCT((RIGHT(A1:A5,1)="S")*LEFT(A1:A5,LEN(A1 :A5)-1))

NOT case sensitive

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"carrielu" wrote:

I am trying to find a formula that will sum the numbers in a group of
cells that have a specific letter in the number: The letter will be
either "A" for one formula or "S" in the other.....

The numbers/letters in a cell look like 5S or 2A - It is for Annual or
Sick Leave on a timesheet

Formula for cells that contain a specific letter
 

I used this formula:
=SUM(IF(B8:AF8<"",SUBSTITUTE(B8:AF8,"S","")+0,0)) shift+control+enter

and it will pull in the totals, but it sums the numbers with S and
numbers w/out - is there away to use this formula and not inlcude the
numbers that do not have letters?

Thanks, Carrie

Formula for cells that contain a specific letter
 

Not a good layout using numbers mixed with text


=SUMPRODUCT(--(0&SUBSTITUTE(B8:AF8,"S","")),--(ISTEXT(B8:AF8)))

if there can be lowercase s you might want to use

=SUMPRODUCT(--(0&SUBSTITUTE(B8:AF8,"S","")),--(ISTEXT(B8:AF8)))


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"carrielu" wrote in message
oups.com...
I used this formula:
=SUM(IF(B8:AF8<"",SUBSTITUTE(B8:AF8,"S","")+0,0)) shift+control+enter

and it will pull in the totals, but it sums the numbers with S and
numbers w/out - is there away to use this formula and not inlcude the
numbers that do not have letters?

Thanks, Carrie

Formula for cells that contain a specific letter
 

The last should have been

=SUMPRODUCT(--(0&SUBSTITUTE(UPPER(B8:AF8),"S","")),--(ISTEXT(B8:AF8)))

sorry


Peo


"Peo Sjoblom" wrote in message
...
Not a good layout using numbers mixed with text


=SUMPRODUCT(--(0&SUBSTITUTE(B8:AF8,"S","")),--(ISTEXT(B8:AF8)))

if there can be lowercase s you might want to use

=SUMPRODUCT(--(0&SUBSTITUTE(B8:AF8,"S","")),--(ISTEXT(B8:AF8)))


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"carrielu" wrote in message
oups.com...
I used this formula:
=SUM(IF(B8:AF8<"",SUBSTITUTE(B8:AF8,"S","")+0,0)) shift+control+enter

and it will pull in the totals, but it sums the numbers with S and
numbers w/out - is there away to use this formula and not inlcude the
numbers that do not have letters?

Thanks, Carrie

Formula for cells that contain a specific letter
 

Yeah! It works great...thank you, thank you, thank you!!!