software

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 quot;Aquot; for one formula or quot;Squot; in the other.....

The numbers/letters in a cell look like 5S or 2A - It is for Annual or
Sick Leave on a timesheetIf the number will on end with 1 letter, then try something like this:

For values in A1:A5

This formula adds the quot;numbersquot; that end in quot;Squot;:
B1: =SUMPRODUCT((RIGHT(A1:A5,1)=quot;Squot;)*LEFT(A1:A5,LEN(A1 :A5)-1))

NOT case sensitive

Does that help?

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

XL2002, WinXP-Proquot;carrieluquot; wrote:

gt; I am trying to find a formula that will sum the numbers in a group of
gt; cells that have a specific letter in the number: The letter will be
gt; either quot;Aquot; for one formula or quot;Squot; in the other.....
gt;
gt; The numbers/letters in a cell look like 5S or 2A - It is for Annual or
gt; Sick Leave on a timesheet
gt;
gt;

I used this formula:
=SUM(IF(B8:AF8lt;gt;quot;quot;,SUBSTITUTE(B8:AF8,quot;Squot;,quot;quot;) 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, CarrieNot a good layout using numbers mixed with text=SUMPRODUCT(--(0amp;SUBSTITUTE(B8:AF8,quot;Squot;,quot;quot;)),--(ISTEXT(B8:AF8)))

if there can be lowercase s you might want to use

=SUMPRODUCT(--(0amp;SUBSTITUTE(B8:AF8,quot;Squot;,quot;quot;)),--(ISTEXT(B8:AF8)))--

Regards,

Peo Sjoblom

nwexcelsolutions.comquot;carrieluquot; gt; wrote in message oups.com...
gt;I used this formula:
gt; =SUM(IF(B8:AF8lt;gt;quot;quot;,SUBSTITUTE(B8:AF8,quot;Squot;,quot;quot;) 0,0)) shift control enter
gt;
gt; and it will pull in the totals, but it sums the numbers with S and
gt; numbers w/out - is there away to use this formula and not inlcude the
gt; numbers that do not have letters?
gt;
gt; Thanks, Carrie
gt;
The last should have been

=SUMPRODUCT(--(0amp;SUBSTITUTE(UPPER(B8:AF8),quot;Squot;,quot;quot;)),--(ISTEXT(B8:AF8)))

sorryPeoquot;Peo Sjoblomquot; gt; wrote in message
...
gt; Not a good layout using numbers mixed with text
gt;
gt;
gt; =SUMPRODUCT(--(0amp;SUBSTITUTE(B8:AF8,quot;Squot;,quot;quot;)),--(ISTEXT(B8:AF8)))
gt;
gt; if there can be lowercase s you might want to use
gt;
gt; =SUMPRODUCT(--(0amp;SUBSTITUTE(B8:AF8,quot;Squot;,quot;quot;)),--(ISTEXT(B8:AF8)))
gt;
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; nwexcelsolutions.com
gt;
gt;
gt; quot;carrieluquot; gt; wrote in message
gt; oups.com...
gt;gt;I used this formula:
gt;gt; =SUM(IF(B8:AF8lt;gt;quot;quot;,SUBSTITUTE(B8:AF8,quot;Squot;,quot;quot;) 0,0)) shift control enter
gt;gt;
gt;gt; and it will pull in the totals, but it sums the numbers with S and
gt;gt; numbers w/out - is there away to use this formula and not inlcude the
gt;gt; numbers that do not have letters?
gt;gt;
gt;gt; Thanks, Carrie
gt;gt;
gt;
gt;
Yeah! It works great...thank you, thank you, thank you!!!