View Single Post
  #7  
Old January 13th, 2010, 07:27 PM posted to microsoft.public.excel.worksheet.functions
Trevor Little[_2_]
external usenet poster
 
Posts: 5
Default Summing Cells with Text and Numbers

Hi Mike,

I just came across one problem with the formula that I haven't been able to
figure out. It doesn't seem to work, if 1 of the cells in the array is
blank... is there a way of putting in a condition where it ignores the blank
cells?

Thanks again.

"Mike H" wrote:

Trevor,

With your 'numbers in a1 - d1 try this

=SUMPRODUCT((LEFT(A11,1)="S")*(RIGHT(A11,LEN(A 11)-1)))

Mike

"Trevor Little" wrote:

Is it possible to use the Sumif function to add numbers with a specific
designator in front of it?

Eg.
A row contains strings like: S8, S10, V5, S5

Is there a formula that can total the S's? (Total would be 23)