Summing Cells with Text and Numbers

[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)

 

[Mike H]

Trevor,

With your 'numbers in a1 - d1 try this

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

Mike

 

[Bernard Liengme]

=SUMPRODUCT((LEFT(A11)="S")*(MID(A11,2,20)))
best wishes

 

[Trevor Little]

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.

 

[Trevor Little]

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.

 

[Bernard Liengme]

The part (LEFT(A11)="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

 

[Trevor Little]

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.

 

[Trevor Little]

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]

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

 

[Trevor Little]

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]

Send me a sample file - get my email from my website
best wishes

 

[Max]

... 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?

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