• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sumifs based on short date format


Active Member
Hi everyone,

I would like to ask you to support me.

The problem : how to "sumifs" the sales view based on "Customer" and "Calendar Month Historical" but the "Calendar Month Relevant" needs to be smaller or equal to the Calendar Month Historical.

The tricky part is that, I can only retrieve the "Calendar Month Relevant" data in this way: MM.YYYY

If possible, I would not use any helper column or array solution.

Please, refer to the attached sample file for the desired result.

Any support you can provide would be greatly appreciated.


  • sample.xlsx
    13.3 KB · Views: 10

1] Replaced all text value cells in Column C Column D and Column H with actual date by

Press "Ctrt+H", Fin what : "." ; Replace with: "/" >> click "Replace all"

2] In J9, formula copied down:


Power Query will easily change dates as required

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer ", type text}, {"Calendar Month Historical", type date}, {"Calendar Month Relevant", type date}, {"Sales View", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Calendar Month Historical]>=[Calendar Month Relevant]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
    #"Removed Columns"
Then build a Pivot Table as required
Now just add data, refresh, and your good to go


  • sample(7).xlsx
    26.8 KB · Views: 3