Sumifs based on Date

[Joe Shaer]

Dear all,

I would like to add 'total price' based on date criteria based on Month & Year.

I figured a way to do it but was wondering if there is an easier formula to work with. Please see attachment.

Any help would be appreciated.

Thanks
Joe

 

[AlanSidman]

Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Sales_Data3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"OrderDate", type date}, {"Region", type text}, {"City", type text}, {"Category", type text}, {"Product", type text}, {"Quantity", Int64.Type}, {"UnitPrice", type number}, {"TotalPrice", type number}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([OrderDate]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([OrderDate]), Int64.Type),
    #"Grouped Rows" = Table.Group(#"Inserted Month", {"Year", "Month"}, {{"Total", each List.Sum([TotalPrice]), type nullable number}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Year", Order.Ascending}, {"Month", Order.Ascending}})
in
    #"Sorted Rows"

 

[bosco_yip]

Or,

Try this copied down formula,

In L2 formula copied down:

=SUMIFS(Sales_Data3[TotalPrice],Sales_Data3[OrderDate],">="&0+("1/"&SUBSTITUTE(MID(J2,17,20),":",)),Sales_Data3[OrderDate],"<="&EOMONTH(SUBSTITUTE(MID(J2,17,20),":",),0))