Forum Discussion

Sachin_Bhangale's avatar
Sachin_Bhangale
Copper Contributor
Jul 09, 2020

Sum excluding text

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 :...
excel
mtarler's avatar
mtarler
Silver Contributor
Jul 09, 2020

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

 

  • Sachin_Bhangale's avatar
    Sachin_Bhangale
    Copper Contributor
    Jul 10, 2020
    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.

Resources