Home » Sum Negative Numbers Only

Sum Negative Numbers Only

Extract the negative numbers and sum them from the given strings. Hence if string is “qw-56&34-09wtey”, then negative numbers are -56 and -09 and sum of these will be -65.

📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 135
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Sum Negative Numbers Only with Power Query

Power Query solution 1 for Sum Negative Numbers Only, proposed by Bo Rydobon 🇹🇭: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Rs = List.Transform(
    Table.ToRows(Source), 
    each List.Sum(
      List.Select(
        List.Transform(
          Splitter.SplitTextByAnyDelimiter({" " .. ","} & {"A" .. "z"})(
            Text.Replace(_{0}, "-", "+-")
          ), 
          Number.From
        ), 
        each _ < 0
      )
        & {0}
    )
  )
in
  Rs
Power Query solution 2 for Sum Negative Numbers Only, proposed by Aditya Kumar Darak 🇮🇳: 
let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Result = Table.AddColumn(
    Source, 
    "Split", 
    each [
      Split1 = List.Skip(Text.Split([String], "-")), 
      Split2 = List.Transform(
        Split1, 
        (f) =>
          Number.From(
            Text.Combine(List.FirstN(Text.ToList(f), (x) => List.Contains({"0" .. "9"}, x)))
          )
      ), 
      Total = - List.Sum(Split2)
    ][Total]
      ?? 0
  )
in
  Result
Power Query solution 3 for Sum Negative Numbers Only, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Sol = Table.AddColumn(
    Source, 
    "Answer", 
    each 
      let
        a = Text.Split([String], "-"), 
        b = if List.Count(a) = 1 then "" else List.Skip(a), 
        c = List.Transform(
          List.Transform(
            b, 
            each Splitter.SplitTextByCharacterTransition(
              {"0" .. "9"}, 
              {"A" .. "Z", "a" .. "z", "+"}
            )(_){0}
          ), 
          Number.From
        )
      in
        try List.Sum(c) * - 1 otherwise 0
  )[[Answer]]
in
  Sol
Power Query solution 4 for Sum Negative Numbers Only, proposed by Luan Rodrigues: 
let
  Source = Tabela1, 
  result = Table.AddColumn(
    Source, 
    "Personalizar", 
    each [
      a = Splitter.SplitTextByAnyDelimiter({" " .. ",", "A" .. "z"})([String]), 
      b = List.Transform(a, each Text.Replace(_, "-", ",-")), 
      c = List.Select(b, each _ <> ""), 
      d = List.Combine(List.Transform(c, each Text.Split(_, ","))), 
      e = List.Select(d, each _ <> "" and Number.From(_) < 0), 
      f = List.Sum(List.Transform(e, Number.From))
    ][f]
  )
in
  result
Power Query solution 5 for Sum Negative Numbers Only, proposed by Bhavya Gupta: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Final = Table.AddColumn(
    Source, 
    "Result", 
    each List.Sum(
      List.Transform(
        Splitter.SplitTextByCharacterTransition(
          (a) => not List.Contains({"-"}, a), 
          (a) => not List.Contains({"0" .. "9"}, a)
        )([String]), 
        each if ((try Number.From(_) otherwise 0) ?? 0) < 0 then Number.From(_) else 0
      )
    )
  )
in
  Final
Power Query solution 6 for Sum Negative Numbers Only, proposed by Venkata Rajesh: 
Custom column with below logic
List.Sum(List.Transform(List.Select(Splitter.SplitTextByCharacterTransition({"0".."9"}, Text.ToList(Text.Remove([String],{"0".."9"})))([String]), each Text.Contains(_,"-")), each Number.From(Text.Select(_,{"0".."9","-"}))))
Power Query solution 7 for Sum Negative Numbers Only, proposed by Jan Willem Van Holst: 
let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WUjY20TU1SyyyMFDVNVeK1YlWcq1ITs2xsDTwDTZDEtAFi+hChXwzk4vyi/PTShTAkmAxXUttcwtdUxNdQwMI39BIF2g4UMwCzLfUNjLWNjYBs1XNzMtTi3QtgSpjAQ==", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [String = _t]
  ), 
  Copy = Table.DuplicateColumn(Source, "String", "String - Copy"), 
  #"split-" = Table.ExpandListColumn(
    Table.TransformColumns(
      Copy, 
      {{"String - Copy", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv)}}
    ), 
    "String - Copy"
  ), 
  Group = Table.Group(
    #"split-", 
    {"String"}, 
    {{"all", each Table.SelectRows(Table.AddIndexColumn(_, "index", 0, 1), (x) => x[index] <> 0)}}
  ), 
  Expanded = Table.ExpandTableColumn(Group, "all", {"String - Copy"}, {"all.String - Copy"}), 
  splitnum = Table.SplitColumn(
    Expanded, 
    "all.String - Copy", 
    Splitter.SplitTextByCharacterTransition({"0" .. "9"}, (c) => not List.Contains({"0" .. "9"}, c)), 
    {"numbers", "Copy.2", "Copy.3"}
  )[[String], [numbers]], 
  ChangedType = Table.TransformColumnTypes(splitnum, {{"numbers", Int64.Type}}), 
  result = Table.Group(ChangedType, {"String"}, {{"Count", each - List.Sum([numbers])}})
in
  result
Power Query solution 8 for Sum Negative Numbers Only, proposed by Chetan Rane: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Added Custom" = Table.AddColumn(
    Source, 
    "Custom", 
    each List.Select(Text.ToList([String]), each Text.Contains("-0123456789", _))
  ), 
  #"Added Custom1" = Table.AddColumn(
    #"Added Custom", 
    "Negative Sum", 
    each List.Sum(
      List.Transform([Custom], each if Text.StartsWith(_, "-") then Number.FromText(_) else 0)
    )
  ), 
  #"Removed Columns" = Table.SelectColumns(#"Added Custom1", {"Negative Sum"})
in
  #"Removed Columns"

Solving the challenge of Sum Negative Numbers Only with Excel

Excel solution 1 for Sum Negative Numbers Only, proposed by Rick Rothstein: 
=MAP(A2:A9,LAMBDA(n,SUM(MAP(DROP(TEXTSPLIT(n&"-0","-"),,1),LAMBDA(x,LOOKUP(0,-LEFT(x,ROW(1:99))))),)))
Excel solution 2 for Sum Negative Numbers Only, proposed by 🇰🇷 Taeyong Shin: 
=LET(
 c, DROP(CHAR(SEQUENCE(125)), 32),
 chr, FILTER(c, ISERR(-c)),
 MAP(A2:A9 & "+", LAMBDA(m, LET(n, TEXTSPLIT(m, , chr, 1), SUM(IF(ISNUMBER(FIND("-", TEXTSPLIT(m, , n, 1))), -n)))))
)
Excel solution 3 for Sum Negative Numbers Only, proposed by 🇰🇷 Taeyong Shin: 
=MAP(A2:A9,LAMBDA(x,SUM(--IFNA(REGEXEXTRACT(x,"-d+",1),))))
Excel solution 4 for Sum Negative Numbers Only, proposed by Kris Jaganah: 
=MAP(A2:A9,LAMBDA(z,LET(a,SEQUENCE(150),b,CHAR(a),c,VSTACK(FILTER(b,(a<48)),FILTER(b,(a>57))),d,LEN(z)-LEN(SUBSTITUTE(z,"-","")),e,IFERROR(TEXTAFTER(z,"-",SEQUENCE(,d),0,1,""),0),-SUM(IFERROR(TEXTBEFORE(e,c),e)/1))))
Excel solution 5 for Sum Negative Numbers Only, proposed by Julian Poeltl: 
=MAP(A2:A9,LAMBDA(N,LET(SP,TEXTSPLIT(N,"-"),BN,IF(ISNUMBER(--LEFT(SP,1)),SP,0),IFERROR(SUM(DROP(MAP(BN,LAMBDA(A,LET(SP,MID(A,SEQUENCE(LEN(A)),1),IN,ISNUMBER(--SP),IFERROR(CONCAT(TAKE(SP,XMATCH(FALSE,IN)-1)),A)))),,1)*-1),0))))
Excel solution 6 for Sum Negative Numbers Only, proposed by Aditya Kumar Darak 🇮🇳: 
=MAP(
 A2:A9,
 LAMBDA(a,
 LET(
 _f, LAMBDA(ME, a, n,
 IF(
 OR(ISERR(-LEFT(a, n + 1)), LEN(a) = n),
 LEFT(a, n),
 ME(ME, a, n + 1)
 )
 ),
 _c, MAP(DROP(TEXTSPLIT(a, , "-"), 1), LAMBDA(x, _f(_f, x, 1))),
 _r, IFERROR(SUM(-_c), 0),
 _r
 )
 )
)
Excel solution 7 for Sum Negative Numbers Only, proposed by Timothée BLIOT: 
=MAP(A2:A9,LAMBDA(x,LET(A,IFERROR(TEXTSPLIT(SUBSTITUTE(x,"-","-"),CHAR(VSTACK(SEQUENCE(44),SEQUENCE(2,,46),SEQUENCE(198,,58))),,1),0)*1,SUM(FILTER(A,A<=0,0)))))
Excel solution 8 for Sum Negative Numbers Only, proposed by Sunny Baggu: 
=MAP(A2:A9,LAMBDA(a,LET(
_q,"Excel Challenge 135",
_modstr,REDUCE(a,VSTACK(CHAR(SEQUENCE(26)+64),CHAR(SEQUENCE(26)+96),CHAR(SEQUENCE(11)+33)),LAMBDA(a,v,SUBSTITUTE(a,v," "))),
_sub,SUBSTITUTE(_modstr,"-"," -"),
_vsub,TOCOL(TEXTSPLIT(_sub,," ")+0,3),
IFERROR(SUM(FILTER(_vsub,_vsub<0)),0))))
Excel solution 9 for Sum Negative Numbers Only, proposed by Md. Zohurul Islam: 
=LET(
z,A2:A9,
sq,SEQUENCE(99),
w,"+"&z,
u,MAP(w,LAMBDA(x,LET(
 a,0&TEXTSPLIT(x,"-"),
 b,IFERROR(-LEFT(a,sq),0),
 d,BYCOL(b,MIN),
 e,SUM(d),
 e)
 )),
u)
Excel solution 10 for Sum Negative Numbers Only, proposed by Guillermo Arroyo: 
=MAP(A2:A9,LAMBDA(_a,LET(_b,MID(_a,SEQUENCE(LEN(_a)),1),_c,--(TEXTSPLIT("0 "&CONCAT(IFERROR(--(DROP(VSTACK("",IF(_b="-","-","")),-1)&_b)," "))," ",,1)),SUM(_c*(_c<0)))))
Excel solution 11 for Sum Negative Numbers Only, proposed by Anup Kumar: 
=BYROW(A2:A9,
LAMBDA(a,
LET(
txt,a,
splt,TEXTSPLIT(txt,"-"),
nonDigitsSet,CHAR(VSTACK(SEQUENCE(47,,1),SEQUENCE(198,,58,1))),
IFERROR(
SUMPRODUCT(-1*TEXTSPLIT(DROP(splt,,1),nonDigitsSet)),
0)
)
)
)
Excel solution 12 for Sum Negative Numbers Only, proposed by Rayan S.: 
=MAP(
 A2:A9,
 LAMBDA(a,
 LET(
 Start, TOROW(DROP(UNIQUE(FIND("-", a, SEQUENCE(LEN(a)))), -1)) + 1,
 Split, TOROW((IFERROR(MID(a, SEQUENCE(LEN(a)), 1) + 0, "A"))),
 End, IFERROR(FIND("A", TEXTJOIN("", , Split), Start), Start),
 chars, IF(End - Start = 0, 2, End - Start),
 sol, SUM(MID(a, Start, chars) + 0),
 IFERROR(-sol, "")
 )
 )
)

Solving the challenge of Sum Negative Numbers Only with Python in Excel

Python in Excel solution 1 for Sum Negative Numbers Only, proposed by Alejandro Campos: 
import re
strings = [
 "#34-56ar80%-7",
 "Excel890MS67",
 "Excel-890MS-67",
 "Microsoft Excel",
 "-9+78-54-10",
 "-12-34-78-8",
 "9+23+34",
 "%67wer-90"
]
def extract_and_sum_negatives(s):
 negatives = re.findall(r'-d+', s)
 negatives = list(map(int, negatives))
 return sum(negatives)
df = pd.DataFrame({
 'String': strings,
 'Sum of Negatives': [extract_and_sum_negatives(s) for s in strings]
})
df

Solving the challenge of Sum Negative Numbers Only with R

R solution 1 for Sum Negative Numbers Only, proposed by Juliano Santos Lima: 
Some Functions
```
import re
def sum_negative_numbers(string): 
 regex = r"-[0-9]+" 
 matches = re.findall(regex, string)
 sum_of_negatives = sum(map(int, matches))
 return sum_of_negatives ```
sum_negative_numbers <- function(string) {
 regex <- "-[0-9]+"
 matches <- regmatches(string, gregexpr(regex, string))[[1]]
 sum_of_negatives <- sum(as.numeric(matches))
 return(sum_of_negatives)
}

&&&

Leave a Reply