Sum excluding text

%3CLINGO-SUB%20id%3D%22lingo-sub-1512078%22%20slang%3D%22en-US%22%3ESum%20excluding%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1512078%22%20slang%3D%22en-US%22%3E%3CP%20class%3D%22%22%3EHi%20team%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFormula%201%20%3A%3C%2FP%3E%3CP%3EA1%20%3A%2010%3C%2FP%3E%3CP%3EA2%20%3A%2020%3C%2FP%3E%3CP%3EA3%20%3A%20O%20-%2015%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA4%20%3A%2045%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20A4%2C%20I%20need%20to%20get%20sum%20of%20A1%3AA3%2C%20excluding%20alphabets.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFormula%202%20%3A%3C%2FP%3E%3CP%3EA1%20%3A%2010%3C%2FP%3E%3CP%3EA2%20%3A%2020%3C%2FP%3E%3CP%3EA3%20%3A%20O%20-%2015%3C%2FP%3E%3CP%3EA4%20%3A%20A%3C%2FP%3E%3CP%3EA5%20%3A%20SL%3C%2FP%3E%3CP%3EA6%20%3A%20PL%3C%2FP%3E%3CP%3EA7%20%3A%20W%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA8%20%3A%20150%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22%22%3EIn%20A8%2C%20I%20need%20to%20output%20which%20will%20exclude%20cells%20have%20%22O%22%20and%20multiple%20numbers%20by%2025.%20As%20in%20A1%20to%20A7%2C%20we%20have%20O%20only%20once%20so%20we%20will%20exclude%20it%2C%20then%20rest%20are%206%20cells%20so%206%20*%2025%20%3D%20150.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%20Can%20you%20please%20help%20me%20with%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1512078%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1512143%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20excluding%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1512143%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F391485%22%20target%3D%22_blank%22%3E%40Sachin_Bhangale%3C%2FA%3E%26nbsp%3B%20if%20that%20format%20%22O%20-%20%5Bnumber%5D%22%20is%20always%20true%20then%20you%20can%20do%20the%20following.%3C%2FP%3E%3CP%3EFormula%201%3A%20(note%20this%20formula%20relies%20on%20%22O%20-%20%22%20being%204%20characters%20before%20the%20number%20starts)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUM(A2%3AA5)%2BSUMPRODUCT(--NOT(ISNUMBER(A2%3AA5))*(0%26amp%3BMID(A2%3AA5%2C5%2C999)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFormula%202%3A%26nbsp%3B%20(although%20you%20said%20just%20%22O%22%20I%20explicitly%20excluded%20only%20%22O%20-%20%22%20incase%20another%20text%20list%20is%20%22ON%22%20or%20something%20like%20that)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMPRODUCT(--NOT((LEFT(%24A%242%3A%24A%245%2C4)%3D%22O%20-%20%22)))*25%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1513225%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20excluding%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1513225%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F391485%22%20target%3D%22_blank%22%3E%40Sachin_Bhangale%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMPRODUCT(SUBSTITUTE(A1%3AA3%2C%22O%20-%22%2C%22%22)*1)%0A%0Aand%0A%0A%3DCOUNTIF(A1%3AA7%2C%22%26lt%3B%26gt%3B*O%20-*%22)*25%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1513606%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20excluding%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1513606%22%20slang%3D%22en-US%22%3EThanks.%3CBR%20%2F%3EFormula%201%20perfectly%20works%20for%20my%20file.%3CBR%20%2F%3EFormula%202%20does%20not%20work%20as%20there%20are%20cells%20which%20have%20other%20characters%20as%20well%20%26amp%3B%20it%20should%20not%20multiply%20only%20cell%20contains%20%22O%22.%20However%2C%20I%20got%20correct%20formula%20from%20another%20user.%3CBR%20%2F%3EThanks.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1513609%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20excluding%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1513609%22%20slang%3D%22en-US%22%3EThanks.%3CBR%20%2F%3EYour%202nd%20formula%20perfectly%20works%20for%20me.%20I%20got%20formula%20for%201%20from%20another%20user.%3CBR%20%2F%3EThanks.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1516558%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20excluding%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1516558%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F391485%22%20target%3D%22_blank%22%3E%40Sachin_Bhangale%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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

@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 

As variant

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

and

=COUNTIF(A1:A7,"<>*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.
Thanks.
Your 2nd formula perfectly works for me. I got formula for 1 from another user.
Thanks.

@Sachin_Bhangale , you are welcome