Create sumif in PowerQuery (not DAX, and not use group by feature)

Add new empty query and paste this into the advanced editor:

let
  Source = #table({"Col1", "Col2", "Col3"}, {{1,"Some", 1}, {1, "other", 2}, {1, "other", 3}, {2, "irrelevant", 4}, {2, "data", 5}}),
    #"Sub total" = Table.AddColumn(Source, "Col4", each List.Sum(Table.Column(Table.SelectRows(Source, (recordFilter) => recordFilter[Col1]=[Col1]), "Col3"))),
    #"Grand total" = Table.AddColumn(#"Sub total", "Col5", each List.Sum(#"Sub total"[Col3]))

in
    #"Grand total"

Then take a look at the "Sub total" to see how it works.

It's specifically this part of the code that does the sub total aka sumif

List.Sum(Table.Column(Table.SelectRows(Source, (recordFilter) => recordFilter[Col1]=[Col1]), "Col3"))

Sub total is calculated over Col1 with Col3 as input.

In sumifs terms (see this link for sum if syntax):

Col3 = sum_range

recordFilter[Col1]=range

[Col1]=criteria

If you need to divide the sub total over several columns, here's an example using col1 and col2: 

List.Sum(Table.Column(Table.SelectRows(Source, (recordFilter) => recordFilter[Col1]=[Col1] and recordFilter[Col2]=[Col2]), "Col3"))