I need to add up a data column (Members) flagged by multiple columns (Year1, Year2, Year3) for each date.
What solution would use the fewest tools?
Input Table
Date | Members | Year1 | Year2 | Year3 |
1-Jan | 1 | 0 | 0 | 1 |
1-Jan | 1 | 0 | 0 | 1 |
1-Jan | 1 | 0 | 1 | 0 |
1-Jan | 1 | 0 | 1 | 0 |
1-Jan | 1 | 1 | 0 | 0 |
1-Jan | 1 | 1 | 0 | 0 |
1-Jan | 1 | 1 | 0 | 0 |
1-Jan | 1 | 1 | 0 | 0 |
1-Feb | 1 | 0 | 0 | 1 |
1-Feb | 1 | 0 | 0 | 1 |
1-Feb | 1 | 0 | 1 | 0 |
1-Feb | 1 | 1 | 1 | 0 |
1-Feb | 1 | 1 | 0 | 0 |
1-Feb | 1 | 1 | 0 | 0 |
1-Mar | 1 | 0 | 1 | 1 |
1-Mar | 1 | 0 | 1 | 0 |
1-Mar | 1 | 1 | 1 | 0 |
1-Mar | 1 | 1 | 1 | 0 |
1-Mar | 1 | 1 | 0 | 0 |
1-Mar | 1 | 1 | 0 | 0 |
1-Apr | 0 | 1 | 1 | 0 |
1-Apr | 0 | 1 | 1 | 0 |
1-Apr | 1 | 1 | 0 | 0 |
1-Apr | 1 | 1 | 1 | 0 |
1-Apr | 1 | 1 | 1 | 0 |
Sum members for Year1, for 1-Jan, 1 + 1 + 1 + 1 = 4.
Sum Members for Year1, for 1-Feb, 1 + 1 + 1 = 3.
Sum Members for Year1, for 1-Mar, 1 + 1 + 1 + 1 = 4.
Sum Members for Year1 (Note the flag variable rules out 2), for 1-Apr, 1 + 1 + 1 =3.
Output Table
Date | MemberYear1 | MemberYear2 | MemberYear3 |
1-Jan | 4 | 2 | 2 |
1-Feb | 3 | 2 | 2 |
1-Mar | 4 | 4 | 1 |
1-Apr | 3 | 2 | 0 |
