Index match sum needed

[tomhiggs98]

Hi, I need to be able to sum the sales of fruits for each month and year. The columns for month and year are dynamic so would need to match the year and month and then sum the sales based on the fruit in column A.

 

[bebo021999]

Would suggest using SUMPRODUCT

 

[tomhiggs98]

Would suggest using SUMPRODUCT

how?

 

[Fluff]

Is this for Excel or Google Sheets?

 

[tomhiggs98]

Is this for Excel or Google Sheets?

Excel

 

[Fluff]

Then why did you post an image from Sheets?

Also I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[alansidman]

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Transposed Table" = Table.Transpose(Source),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Transposed Table", {{"Column1", type text}}, "en-US"),{"Column2", "Column1"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {":"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {":", "Attribute"}, {{"Totals", each List.Sum([Value]), type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{":", "Fruit"}})
in
    #"Renamed Columns"

 

[bebo021999]

how?

Like this:

=SUMPRODUCT( (Col_A=fruitname) * (row 2 = year) * (row 3=month) * data range)

adapt it to your actual range