Sumifs based on short date format

[Villalobos]

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.

 

[bosco_yip]

Try

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:

=SUMIFS(E:E,B:B,G9,C:C,H9,D:D,"<="&H9)

 

[pecoflyer]

Power Query will easily change dates as required

let
    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"})
in
    #"Removed Columns"

Then build a Pivot Table as required
Now just add data, refresh, and your good to go

 

[Villalobos]

Thanks a lot guys for your support.
I am waiting a bit. Maybe someone has another formula solution.

 

[pecoflyer]

I am waiting a bit. Maybe someone has another formula solution

What's wrong with Bosco's suggestion ?
Power Query solution avoids having to recreate dates