Sum Output per Year

Dear all

How would calculate the total Quantity for year 2025 if the data set has to be arranged like this:

Excel 1.PNG

Many thanks for your ideas and support!
Kind regards

Excel Example.xlsx

You could use FILTER or SUMPRODUCT to sum values where the header contains "Quantity". Assuming the " pcs" is text rather than a Custom Format, you could use SUBSTITUTE to remove it. Your picture only shows months in 2025. Are there other years?


Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

It would be easier to do your analysis if you normalize your data. That would allow you to Pivot your data

A better layout would be

Column A. Date
Column B Quantity. Avoid the use of the term pcs. and keep it to numbers only. Once you put the term pcs in the cell it changes the data to Text. Put that term in the header, ie. "Quantity PCS"

in M3

Formula:

=SUMPRODUCT((YEAR($A3:$K3)=M$2)*(SUBSTITUTE($B3:$L3,"pcs","")))

copy across and down

For All rows

Formula:

=SUMPRODUCT((YEAR($A4:$K9)=M$2)*(SUBSTITUTE($B4:$L9,"pcs","")))

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Quantity", each if Text.Contains([Attribute],"Quantity") then [Value] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom",{"Quantity"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each not Text.StartsWith([Attribute], "Quantity")),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Value", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Value]), Int64.Type),
    #"Grouped Rows" = Table.Group(#"Inserted Year", {"Year"}, {{"Total Quantity", each List.Sum([Quantity]), type number}})
in
    #"Grouped Rows"

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.

Excel 2016 (Windows) 64 bit

	Q	R
1	Year	Total Quantity
2	2022	36
3	2023	470
4	2024	73
5	2025	365
6	2026	70

Sheet: Sheet1

One option with FILTER

Formula:

=IFERROR(SUM(FILTER($B3:$L3,YEAR($A3:$K3)=M$2),0),"")

copied across and down.