Sum excluding text

[Sachin_Bhangale]

Hi team, 

Formula 1 :

A1 : 10

A2 : 20

A3 : O - 15

A4 : 45

In A4, I need to get sum of A1:A3, excluding alphabets. 

Formula 2 :

A1 : 10

A2 : 20

A3 : O - 15

A4 : A

A5 : SL

A6 : PL

A7 : W

A8 : 150

In A8, I need to output which will exclude cells have "O" and multiple numbers by 25. As in A1 to A7, we have O only once so we will exclude it, then rest are 6 cells so 6 * 25 = 150.

@Riny_van_Eekelen  Can you please help me with this?

Thanks in advance

[#2]

@Sachin_Bhangale  if that format "O - [number]" is always true then you can do the following.

Formula 1: (note this formula relies on "O - " being 4 characters before the number starts)

=SUM(A2:A5)+SUMPRODUCT(--NOT(ISNUMBER(A2:A5))*(0&MID(A2:A5,5,999)))

Formula 2:  (although you said just "O" I explicitly excluded only "O - " incase another text list is "ON" or something like that)

=SUMPRODUCT(--NOT((LEFT($A$2:$A$5,4)="O - ")))*25

[#3]

@Sachin_Bhangale 

As variant

=SUMPRODUCT(SUBSTITUTE(A1:A3,"O -","")*1)

and

=COUNTIF(A1:A7,"<>*O -*")*25

[#4]

Thanks.
Formula 1 perfectly works for my file.
Formula 2 does not work as there are cells which have other characters as well & it should not multiply only cell contains "O". However, I got correct formula from another user.
Thanks.

[#5]

Thanks.
Your 2nd formula perfectly works for me. I got formula for 1 from another user.
Thanks.

[#6]

@Sachin_Bhangale , you are welcome