Hi, I'm trying to simplify a data range. I have data from 1970 up to 2024. For each year, I have 11 different categories. I'm looking for a way to add together those 11 datapoints into one, in order to have a simpler range to use. I've tried to set things up so that my sumif function would use the years in E2 and forwards, but I've only been able to make things work by manually inputing "1970" (see E3). Is there a way to set up my function so that it can auto fill? Also, sorry for the french excel, SUMIF = SOMME.SI and SUMIFS = SOMME.SI.ENS . Thanks for the help!
| Classeur1 | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 1 | Year | Name | Amount (t) | |||||||||
| 2 | 1970 | Biomass | 275983422,9 | 1970 | 1971 | 1972 | 1973 | 1974 | 1975 | |||
| 3 | 1970 | Excavated earthen materials (including soil) nec | 0 | 1426761240 | ||||||||
| 4 | 1970 | Fossil fuels | 506118342 | 0 | ||||||||
| 5 | 1970 | Metal ores | 11699949,38 | |||||||||
| 6 | 1970 | Mixed and complex products nec. | 16483 | |||||||||
| 7 | 1970 | Non-metallic minerals | 630906224,9 | |||||||||
| 8 | 1970 | Products from biomass | 2036818,1 | |||||||||
| 9 | 1970 | Products from fossil fuels | 0 | |||||||||
| 10 | 1970 | Products from metals | 0 | |||||||||
| 11 | 1970 | Products from non-metallic minerals | 0 | |||||||||
| 12 | 1970 | Waste for final treatment and disposal | 0 | |||||||||
| 13 | 1971 | Biomass | 279474673 | |||||||||
| 14 | 1971 | Excavated earthen materials (including soil) nec | 0 | |||||||||
| 15 | 1971 | Fossil fuels | 500802802 | |||||||||
| 16 | 1971 | Metal ores | 10861919,78 | |||||||||
| 17 | 1971 | Mixed and complex products nec. | 21701 | |||||||||
| 18 | 1971 | Non-metallic minerals | 660877855 | |||||||||
| 19 | 1971 | Products from biomass | 2009277,96 | |||||||||
| 20 | 1971 | Products from fossil fuels | 0 | |||||||||
| 21 | 1971 | Products from metals | 0 | |||||||||
| 22 | 1971 | Products from non-metallic minerals | 0 | |||||||||
| 23 | 1971 | Waste for final treatment and disposal | 0 | |||||||||
Allemagne | ||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E3 | E3 | =TRANSPOSE(SUMIFS(_2024_02_04_DomesticMaterialConsumptionofGermanyin1970_2024bymaterialgroup[Amount (t)],_2024_02_04_DomesticMaterialConsumptionofGermanyin1970_2024bymaterialgroup[Year],"=1970")) |
| E4 | E4 | =SUMIFS(C:C,A:A,"=A2") |
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| DonnéesExternes_5 | =Allemagne!$A$1:$C$606 | E4 |
