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

Good morning Bernard,

I triedusing the MID formula and it is still turning up a result of
"#VALUE!" when a cell is blank.

The formula I am using is:
=SUMPRODUCT((LEFT(C29:AG29,1)="S")*VALUE((MID(C29: AG29,2,20))))
The data that is in those cells are the same as below: (EG. V8, S10, T6, B4,
S4... etc and blanks)

Thanks again for your patience.
Trevor

"Bernard Liengme" wrote:

=SUMPRODUCT((LEFT(A11)="S")*VALUE((MID(A11,2,2 0))))
VALUE is needed if there are any empty cells
Bernard

"Trevor Little" wrote in message
...
Hi Bernard,

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.

"Bernard Liengme" wrote:

=SUMPRODUCT((LEFT(A11)="S")*(MID(A11,2,20)))
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Trevor Little" Trevor wrote in
message
...
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)

.

.