And whenever a new project is added, you have to redesign your workbook - again and again!

Design your workbook more database-style instead! E.g.:

You have a sheet where all projects are registered (E.eg. in defined Table tProjects: Project, [ProjectDate]/[ProjectYear]/ProjectStartPeriod]/[ProjectEndPeriod] - you have to decide how you indicate the period the project applies to. In case you want the workbook have the data of several years, the periods must have year included - like 202201 for January of current year. In case you start a new workbook every year, you can omit the year number). You have to define the Project column of Projects table as dynamic Named Range, to use it in Data Validation lists in other worksheets (e.g. lProjects);

You have a sheet where all components used in any project are registered (E.g. in Table tComponents: Component). The Column Component must be defined as Dynamic Named range (e.g. lComponents)

You have a sheet where all project components are registered (E.g. in Table tProjComp: Project, Component, Period, Amount). Project column is defined as Data Validation list, based on lProjects, so only registered projects can be added. Component column is also defined as Data Validation List, based on lComponents. In case periods you will use are always only four ones ("Autumn", "Winter", "Spring" and "Summer"), you can define data validation list for Period column directly. In case periods must be determined e.g. for specific year, you need a separate worksheet with Table (tPeriods: Period) where those periods are defined manually (in format like "2022-Summer" or 202201, or whatever), or are calculated, and where a dynamic named range containing defined periods is created, to be used as source for Data Validation list;

You can create a report sheet, where you select a project, and all component info from tProjComp for this project is displayed - or you simply set the filter for this component on tProcComp, and you get same list displayed;

In tProjects Table, you can have additional columns, where various totals from Table tProjComp for every project are calculated (essentially what you aseked in this post for).

You can have any number of other report sheets designed (e.g. a report which returns a list of components needed in selected period, along with totals of needed quantities - a simple SUMIFS() will give you those totals).

You design it in this way, and it will work for years for you. You either create an empty copy of it for every year, and fill it with data for this year, or you use same workbook for many years, and whenever it slows down, you delete rows of old data you don't need anymore, or you archive those data in another workbook of similar design, and remove them from working one.