Jul 09 2020 08:15 AM
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
Jul 09 2020 08:38 AM - edited Jul 09 2020 08:41 AM
@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
Jul 09 2020 03:52 PM
As variant
=SUMPRODUCT(SUBSTITUTE(A1:A3,"O -","")*1)
and
=COUNTIF(A1:A7,"<>*O -*")*25
Jul 09 2020 09:43 PM
Jul 09 2020 09:44 PM