# SUMIFS in Power Query



## ajw5173 (May 20, 2016)

Hi,

Is there a way to add a column within my power query that does a sumifs of other data in the dataset?  I would need 4 criteria to be in the formula.

I appreciate any help I can get with this issue!


----------



## Scrappy666 (May 20, 2016)

If you insert a column next to the power query, it should let you type a formula whilst being part of the table (it will stay green).
This means the formula will automatically drag down to the length of the query.

Your other option is to write it into the SQL (assuming that's what you're writing in).


----------



## ajw5173 (May 20, 2016)

Ideally I would like to have inserting a column with this sumifs in it as a step in my power query using M.


----------



## ajw5173 (May 20, 2016)

=SUMIFS(J:J,O:O,O2,A:A,"closed",C:C,C2,N:N,M2) is the formula I am trying to replicate in Power Query.

a                           b         c                   d                e                         f           g                h              j              k           l              m          n           o

sectiontransfer_dateart_trdepartmentstylevendorCOLORSIZEquan_trretailcostorig_trdest_trnum_trconsign7/21/20158888234553SPORTSWEARLWTS0002FRAME SWNAVYS19835.644003644051263560consign7/21/20158888248433SPORTSWEARINT55550-EXCLRAILSPAT-PLAIDP114850.8444003644051263560consign7/21/20158888237630SPORTSWEARRW55624RAILSDENIM-LTP113850.8444003644051263560consign7/21/20158888249187SPORTSWEARSH2414-EXCLLOVE SAM SWNAVYS12288044003644051263563consign7/21/20158888229565SPORTSWEAR889-R1234-EXCLJOIEWHITEL123890.9544003644051263563consign7/29/20158888237641SPORTSWEARRW55799-ROCSIRAILSPAT-STRIPES113850.8444000244051263891consign7/29/20158888230495SPORTSWEARRW35725-BRITTRAILSPAT-STRIPEP112848.7244000244051263891

<colgroup><col><col><col><col span="8"><col><col><col></colgroup><tbody>

</tbody>


----------



## Tyler Hilton (May 20, 2016)

You could build a power query function that does your sumif and then use the 4 criteria as your variables.  Then insert this function into your original add column procedure and reference the 4 criteria columns.  Have you used functions in power query before?  Here is a resource link if not.  

Best,

Tyler Hilton


----------



## ajw5173 (May 20, 2016)

Hi Tyler,

Thanks for your response.  I haven't really built any functions in Power Query other than an If function.  I don't really know where to even begin a sumif/sumifs or using variable.


----------



## Tyler Hilton (May 20, 2016)

Begin by building a query of your table and filtering it on 4 of the criteria (i.e. "63560","closed","8888234553","440512"). Not sure if I am getting the columns right.  Then use the Group By transformation on this table by these 4 columns and produce a total sum of your cost.  Drill down on that sum by right clicking -> drill down.  In the advanced editor, place the following above your let statement:

(vCriteria1,vCriteria2,vCriteria3,vCriteria4) =>

Also in the advanced editor, change your filter values to these variables above (i.e vCriteria2 instead of "Closed").  Once complete, close your query and name it as fnSUMIF.  Then open a blank query, and have 

Source = fnSUMIF("63560","closed","8888234553","440512")

This should result in the sumif value, barring any datatype issues.

Hope this helps get you started.

Best,

Tyler Hilton


----------

