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

[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


"carrielu" <[email protected]> wrote in message
news:[email protected]...
>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]

The last should have been

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

sorry


Peo


"Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>>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
>>
>
>

[carrielu]

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