Forum Discussion
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 : 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
5 Replies
As variant
=SUMPRODUCT(SUBSTITUTE(A1:A3,"O -","")*1) and =COUNTIF(A1:A7,"<>*O -*")*25- Thanks.
Your 2nd formula perfectly works for me. I got formula for 1 from another user.
Thanks.Sachin_Bhangale , you are welcome
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- 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.
