# SUMIFS functionality using Power Query M advanced editor



## Blackban88s

Hello all, in need of some "M" wizardry to recreate the functionality of this SUMIFS formula. My current skill level with "M" consists of hastily key-word searching the M formula language .pdf so a SUMIF is way over my head. I'm using this SUMIF to compute a running daily balance based on each days transactions. This is the SUMIFS in question:

     Where "[Index]" is the table row count (1...n+1) in ascending order.

         "=SUMIFS([Total Amt],[Acct No. Short],[@[Acct No. Short]],[Value Date],"<="&[@[Value Date]],[Index],"<="&[@Index])" 

Is this possible to do? The current table formula is working fine except when refreshing the query, the table formula doesn't automatically copy-down to the newly added rows of the table.


----------



## Ozeroth

Hi,
It can definitely be done. 

There is a similar sort of example on Ken Puls' Blog here:
Create Running Totals in Power Query


Here's how I would do it, but I'm sure there are other ways!

I'm assuming your source table has these columns:


IndexAcc. No ShortValue DateTotal Amt

<tbody>

</tbody>


Here is some M code that should do what you've described  - test it out  (assuming the source table is named Table1 in Excel):



		Code:
__


let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Acc No. Short", Int64.Type}, {"Value Date", type date}, {"Total Amt", type number}}),


[COLOR=#ff0000][B]    // Start function definition[/B]

[B]    MySUMIFFunction = (InputTable as table, MyDate as date, MyAccount as number, MyIndex as number) as number =>[/B]
[B]                      let[/B]
[B]                         FilteredTable = Table.SelectRows(InputTable, each ([Value Date] <= MyDate and [Acc No. Short] = MyAccount and [Index] <= MyIndex)),[/B]
[B]                         SumOfAmt = List.Sum( Table.Column(FilteredTable, "Total Amt") )[/B]
[B]                      in[/B]
[B]                         SumOfAmt,[/B]

[B]   // End function definition [/B]
[/COLOR]

    AddedSUMIFS = Table.AddColumn(#"Changed Type", "SUMIFS", each MySUMIFFunction(#"Changed Type", [Value Date], [Acc No. Short], [Index]))
in
    AddedSUMIFS


The important bit is the "MySUMIFFunction" function definition in red.
The code basically defines the function to sum Total Amt over rows with Index <= current row, Date <= current row and Account = current row.
Then it adds a column to the original table, calling this function for every row.


----------



## miguel.escobar

this might help you:
ABC Analysis with Power Query - Powered Solutions

the step #5 should give you everything that you need to do a SUMIF:
List.Sum( List.Select(MyList, (x) => x>=[Total Sales])))


----------



## DickyMoo

Not sure why the OP never responded, but this has helped me enormously, thank you both.

Rich


----------

