# I there a SUMIFS equivalent in Power Query?



## JVL (Nov 20, 2021)

Is there an equivalent for SUMIFS formula in Power Query? I would like to calculate the yellow column in Power Query which includes a cumulative sum for each account for each month and year. Grouping the columns does not work as I want to have both the monthly and the cumulative value in the data. How do I do this?


----------



## JGordon11 (Nov 20, 2021)

```
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl = Table.AddColumn(Source, "YTD", (x)=> List.Sum(Table.SelectRows(Source, each _[Account] = x[Account] and _[Month]<=x[Month])[Value]))
in
    tbl
```

Book4ABCDEFGHIJK1AccountYearMonthValueAccountYearMonthValueYTD2A20201100A202011001003A20202100A202021002004A20203100A202031003005A20204100A202041004006A20205100A202051005007A20206250A202062507508A20207100A202071008509A20208100A2020810095010A20209100A20209100105011A202010100A202010100115012A202011100A202011100125013A202012100A202012100135014B20201100B2020110010015B20202100B2020210020016B20203100B2020310030017B2020490B202049039018B20205100B2020510049019B20206100B2020610059020B20207100B2020710069021B20208100B2020810079022B20209100B2020910089023B202010100B20201010099024Sheet2


----------



## JVL (Nov 20, 2021)

This works perfectly! Thank you! I even managed to make YTD for different years. 

Could you be able to provide a brief run through of the formula? And do you have suggestions how to calculate last twelve months if I have data for several years?


----------



## JGordon11 (Nov 20, 2021)

YW.  The Table.SelectRows returns a filtered table, in this case filtered to Account= Account name in row x AND Month <= the Month in row x.
(so as it steps through the rows the filtered table will have just one row for row 0, two for row 1 ... twelve for row11, one for row 12, two for row 13, ...)

List.Sum adds up the Value column in that filtered table.

Table.AddColumn adds the YTD column consisting of each of those List.Sum values.

If you send an xl2bb (or something else that I can paste into excel, i.e., not a picture) of a sample initial table and what the transformed table should look like,  I'll revise the query based on the examples.


----------



## JVL (Nov 21, 2021)

Your help is much appreciated!

Attached is a sample of a situation I'm usually dealing. The first table is the source data which can come from external excels or source sheets in the same workbook. First I usually need to make some modifications to the data in Power Query such as separating company and account and time to years and months. Sometimes these modifications need to be more complex because I usually receive data in different formats. Only after the modifications I would need to add YTD and LTM columns to the query by company, account, year and month.

Thank you in advance!

Sample.xlsxABCDEFGHIJKL1ItemTime Value CompanyAccount nameAccount numberYearMonthValueYTD by company, account, year and monthLTM by company, account, year and month22Company A - Account 1002020-0150Company A Account10020201503Company A - Account 1002020-0220Company A Account10020202204Company A - Account 1002020-0320Company A Account10020203205Company A - Account 1002020-0420Company A Account10020204206Company A - Account 1002020-0520Company A Account10020205207Company A - Account 1002020-0620Company A Account10020206208Company A - Account 1002020-0720Company A Account10020207209Company A - Account 1002020-08100Company A Account1002020810010Company A - Account 1002020-0920Company A Account100202092011Company A - Account 1002020-1020Company A Account1002020102012Company A - Account 1002020-1120Company A Account1002020112013Company A - Account 1002020-1220Company A Account1002020122014Company A - Account 2002020-0140Company A Account200202014015Company A - Account 2002020-0240Company A Account200202024016Company A - Account 2002020-0340Company A Account200202034017Company A - Account 2002020-0440Company A Account200202044018Company A - Account 2002020-0540Company A Account200202054019Company A - Account 2002020-0640Company A Account200202064020Company A - Account 2002020-07(200)Company A Account20020207(200)21Company A - Account 2002020-0840Company A Account200202084022Company A - Account 2002020-0940Company A Account200202094023Company A - Account 2002020-10300Company A Account20020201030024Company A - Account 2002020-1140Company A Account2002020114025Company A - Account 2002020-1240Company A Account2002020124026Company A - Account 1002019-0110Company A Account100201911027Company A - Account 1002019-0210Company A Account100201921028Company A - Account 1002019-0310Company A Account100201931029Company A - Account 1002019-0410Company A Account100201941030Company A - Account 1002019-0510Company A Account100201951031Company A - Account 1002019-0610Company A Account100201961032Company A - Account 1002019-0710Company A Account100201971033Company A - Account 1002019-0850Company A Account100201985034Company A - Account 1002019-0910Company A Account100201991035Company A - Account 1002019-1010Company A Account1002019101036Company A - Account 1002019-1110Company A Account1002019111037Company A - Account 1002019-1210Company A Account1002019121038Company A - Account 2002019-0120Company A Account200201912039Company A - Account 2002019-0220Company A Account200201922040Company A - Account 2002019-0320Company A Account200201932041Company A - Account 2002019-0420Company A Account200201942042Company A - Account 2002019-0520Company A Account200201952043Company A - Account 2002019-0620Company A Account200201962044Company A - Account 2002019-07(100)Company A Account20020197(100)45Company A - Account 2002019-0820Company A Account200201982046Company A - Account 2002019-0920Company A Account200201992047Company A - Account 2002019-10150Company A Account20020191015048Company A - Account 2002019-1120Company A Account2002019112049Company A - Account 2002019-1220Company A Account2002019122050Company B - Account 1002020-0180Company B Account100202018051Company B - Account 1002020-0280Company B Account100202028052Company B - Account 1002020-0380Company B Account100202038053Company B - Account 1002020-0480Company B Account100202048054Company B - Account 1002020-0580Company B Account100202058055Company B - Account 1002020-0680Company B Account100202068056Company B - Account 1002020-0780Company B Account100202078057Company B - Account 1002020-08400Company B Account1002020840058Company B - Account 1002020-0980Company B Account100202098059Company B - Account 1002020-1080Company B Account1002020108060Company B - Account 1002020-1180Company B Account1002020118061Company B - Account 1002020-1280Company B Account1002020128062Company B - Account 2002020-01160Company B Account2002020116063Company B - Account 2002020-02160Company B Account2002020216064Company B - Account 2002020-03160Company B Account2002020316065Company B - Account 2002020-04160Company B Account2002020416066Company B - Account 2002020-05160Company B Account2002020516067Company B - Account 2002020-06160Company B Account2002020616068Company B - Account 2002020-07(800)Company B Account20020207(800)69Company B - Account 2002020-08160Company B Account2002020816070Company B - Account 2002020-09160Company B Account2002020916071Company B - Account 2002020-101,200Company B Account2002020101,20072Company B - Account 2002020-11160Company B Account20020201116073Company B - Account 2002020-12160Company B Account20020201216074Company B - Account 1002020-0160Company B Account100202016075Company B - Account 1002020-0260Company B Account100202026076Company B - Account 1002020-0360Company B Account100202036077Company B - Account 1002020-0460Company B Account100202046078Company B - Account 1002020-0560Company B Account100202056079Company B - Account 1002020-0660Company B Account100202066080Company B - Account 1002020-0760Company B Account100202076081Company B - Account 1002020-08120Company B Account1002020812082Company B - Account 1002020-0960Company B Account100202096083Company B - Account 1002020-1060Company B Account1002020106084Company B - Account 1002020-1160Company B Account1002020116085Company B - Account 1002020-1260Company B Account1002020126086Company B - Account 2002019-0140Company B Account200201914087Company B - Account 2002019-0240Company B Account200201924088Company B - Account 2002019-0340Company B Account200201934089Company B - Account 2002019-0440Company B Account200201944090Company B - Account 2002019-0540Company B Account200201954091Company B - Account 2002019-0640Company B Account200201964092Company B - Account 2002019-0740Company B Account200201974093Company B - Account 2002019-08200Company B Account2002019820094Company B - Account 2002019-0940Company B Account200201994095Company B - Account 2002019-1040Company B Account2002019104096Company B - Account 2002019-1140Company B Account2002019114097Company B - Account 2002019-1240Company B Account2002019124098Company B - Account 1002019-0180Company B Account100201918099Company B - Account 1002019-0280Company B Account1002019280100Company B - Account 1002019-0380Company B Account1002019380101Company B - Account 1002019-0480Company B Account1002019480102Company B - Account 1002019-0580Company B Account1002019580103Company B - Account 1002019-0680Company B Account1002019680104Company B - Account 1002019-07(400)Company B Account10020197(400)105Company B - Account 1002019-0880Company B Account1002019880106Company B - Account 1002019-0980Company B Account1002019980107Company B - Account 1002019-10600Company B Account100201910600108Company B - Account 1002019-1180Company B Account10020191180109Company B - Account 1002019-1280Company B Account10020191280110Company B - Account 1002019-0130Company B Account1002019130111Company B - Account 1002019-0230Company B Account1002019230112Company B - Account 1002019-0330Company B Account1002019330113Company B - Account 1002019-0430Company B Account1002019430114Company B - Account 1002019-0530Company B Account1002019530115Company B - Account 1002019-0630Company B Account1002019630116Company B - Account 1002019-0730Company B Account1002019730117Company B - Account 1002019-0860Company B Account1002019860118Company B - Account 1002019-0930Company B Account1002019930119Company B - Account 1002019-1030Company B Account10020191030120Company B - Account 1002019-1130Company B Account10020191130121Company B - Account 1002019-1230Company B Account10020191230DATA


----------



## JGordon11 (Nov 21, 2021)

try  (make sure Value column name is "Value" and not " Value ")


```
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split1 = Table.SplitColumn(Source, "Item", Splitter.SplitTextByDelimiter(" - ", QuoteStyle.Csv), {"Company", "AccountName"}),
    Split2 = Table.SplitColumn(Split1, "AccountName", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"AccountName", "AccountNumber"}),
    Split3 = Table.SplitColumn(Split2, "Time", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Year", "Month"}),
    tblType = Table.TransformColumnTypes(Split3,{{"Year", Int64.Type}, {"Month", Int64.Type}, {"Value", type number}}),
    tbl = Table.AddColumn(tblType, "YTD", (x)=> List.Sum(Table.SelectRows(tblType, each _[Company] = x[Company] and _[AccountNumber] = x[AccountNumber] and 
        _[Year]=x[Year] and  _[Month]<=x[Month])[Value])),
    tbl1 = Table.AddColumn(tbl, "LTM", (x)=> List.Sum(Table.SelectRows(tbl, each _[Company] = x[Company] and _[AccountNumber] = x[AccountNumber] and 
        #date(_[Year],_[Month],1) <=#date(x[Year],x[Month],1) and #date(_[Year]+1,_[Month],1)> #date(x[Year],x[Month],1))[Value]))
in
    tbl1
```


----------



## JVL (Nov 21, 2021)

This is excellent and works perfectly! Thank you!


----------

