Summing Cells with Text and Numbers

  • Thread starter Thread starter Trevor Little
  • Start date Start date
T

Trevor Little

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)
 
Trevor,

With your 'numbers in a1 - d1 try this

=SUMPRODUCT((LEFT(A1:D1,1)="S")*(RIGHT(A1:D1,LEN(A1:D1)-1)))

Mike
 
Thanks a lot Mike, the formula worked perfectly. How does the "Len(x:x)"
Formula work? is it just the leftovers of the text?

(Just as one addition, I found that it wouldn't quite work for me until I
used the "Value(...)" formula in front of the number I wanted to use.)

Thanks again.
 
Thanks Bernard, This formula worked perfectly for me as well.

The same with the note I left on Mike's formula, I also had to use the
"value(..)" formula in order to have it it take the last part of the number
as a "number" value.

Thanks again.
 
The part (LEFT(A1:D1)="S") will return Boolean FALSE/TRUE
But when Excel does math (here multiplication) on Boolean it converts this
to 0/1
Similarly text that can be converted to numbers will get cohered to numbers
with in a math operation.
So VALUE is not needed
best wishes
Bernard
 
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.
 
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.
 
=SUMPRODUCT((LEFT(A1:D1)="S")*VALUE((MID(A1:D1,2,20))))
VALUE is needed if there are any empty cells
Bernard
 
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
 
... 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?
Click to expand...

Try this variant, array-enter (press CTRL+SHIFT+ENTER):
=SUM(IF(NOT(ISBLANK(A1:D1)),IF(LEFT(A1:D1,1)="S",RIGHT(A1:D1,LEN(A1:D1)-1)+0)))
 
Back
Top