• 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

    Hui...

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

SumProduct As SumIfs Criteria

AlanSidman

Well-Known Member
If you are willing to use Power Query, then here is a solution for you. If your data was in a normalized table, then this would be a lot easier and could be resolved using a Pivot Table. I would urge you to change your file layout to make analysis easier.

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filled Down" = Table.FillDown(Source,{"Column1"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filled Down", [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"JAN", "Month"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Month] <> "TOTAL") and ([KILO METERS] <> 0 and [KILO METERS] <> "KILO METERS")),
    #"Inserted Multiplication" = Table.AddColumn(#"Filtered Rows", "Multiplication", each [KILO METERS] * [#"COST/KM"], type number),
    #"Grouped Rows" = Table.Group(#"Inserted Multiplication", {"Month"}, {{"Total", each List.Sum([Multiplication]), type number}}),
    #"Calculated Sum" = List.Sum(#"Grouped Rows"[Total]),
    #"Converted to Table" = #table(1, {{#"Calculated Sum"}})
in
    #"Converted to Table"
 

AlanSidman

Well-Known Member
I have given you two solutions and have no more for you with the current layout as mentioned earlier as your data is not normalized. Given the current layout, here is information on Power Query

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.
 
Top