Sum cells that have numbers and letters in them!

[anthgav]

Hi
I am struggling to find a formula for the following can anyone help!
As an example I have a row with the following numbers and letters in each cell
I would like to sum the whole row but only add the numbers in the cells that have SC in them. The three cells that have SC in them would total 7.
Can anyone help with a formula thanks!

2SC, 8, 8, 1SC, 4SC,

[DonkeyOte]

If we assume values in A1:E1 then

F1: =SUMPRODUCT(--(RIGHT(A1:E1,2)="SC"),--SUBSTITUTE(A1:E1,"SC",""))

[anthgav]

Thank you for your time

That formula works fine but I have a few problems with it.
If I have a blank cell or a cell with just SC in it the formula does not work can you tweek it so it does

much appreciated!

[DonkeyOte]

Sure, just try adding a 0 to each value in the calculation, eg:

Please Login or Register  to view this content.

If you may have other text string values that need ignoring then it might be worth reverting to an Array - though pending strings it's unlikely to be 100% watertight.

Please Login or Register  to view this content.

[anthgav]

My problem is solved

I ended up using the array as I had other values in there to

Thats brilliant thanks