Forum Discussion
Excel problem-POJ
I have an excel spreadsheet that consists of 7,500 rows sorted by station and year with associated values. I have sorted the same station and the same year to stand together (see example below). I want to find an automated way (formula, function, etc.) to calculate THE SUM of the values for each station for the same year. I have tried various Excel functions without success.
How can it be resolved?
station | year | value | SUM |
GE10 | 2008 | 1 | 1 |
GE10 | 2013 | 5 | 5 |
GE10 | 2024 | 2 | 2 |
GE10 | 2016 | 2 |
|
GE10 | 2016 | 7 | 9 |
GE10 | 2023 | 3 | 3 |
GE11 | 2013 | 1 | 1 |
GE11 | 2016 | 1 |
|
GE11 | 2016 | 3 |
|
GE11 | 2016 | 2 | 6 |
GE12 | 2008 | 1 | 1 |
GE13 | 2013 | 3 |
|
GE13 | 2013 | 2 |
|
GE13 | 2013 | 1 |
|
GE13 | 2013 | 4 | 10 |
GE13 | 2014 | 2 | 2 |
GE11 | 2022 | 1 | 1 |
GE13 | 2013 | 3 |
|
GE13 | 2013 | 1 |
|
GE13 | 2013 | 4 |
|
GE13 | 2013 | 5 |
|
GE13 | 2013 | 2 | 15 |
GE12 | 2013 | 1 | 1 |
GE10 | 2008 | 4 | 4 |
Use the following formula to calculate and display the SUM once for each unique combination:
=IF(AND(A2<>A1, B2<>B1), SUMIFS(C:C, A:A, A2, B:B, B2), "")
Drag down the formula in Column D to fill the rest of the rows.
This approach ensures that the sum appears only once for each station-year combination, specifically at the last occurrence of that combination.
Another efficient way to handle this is by using a Pivot Table, please let me know if need for that.Thank you very much for the Excel information
POJ
