Formula for cells that contain a specific letter

[carrielu]

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

 

[Ron Coderre]

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]

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

 

[Peo Sjoblom]

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

 

[Peo Sjoblom]

The last should have been

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

sorry


Peo

 

[carrielu]

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