Google Sheets question.
I have the following sheet which can grow by adding both rows and columns. The first cell of added columns can be either red or blue, randomly:
MonthYear | red | blue | blue | red
Jan/2017 | 100 | 200 | 10 | 20
Feb/2017 | 800 | 900 | 50 | 60
The result I need should hold the sum for each color:
MonthYear | red | blue
Jan/2017 | 120 | 210
Feb/2017 | 860 | 950
How can I do this with formulas that update the result automatically when adding new rows/columns in the data sheet?
Thanks a bunch!
Single formula solution, paste the formula into another sheet:
={FILTER(Sheet1!A:A,Sheet1!A:A<>""),{TRANSPOSE(UNIQUE(TRANSPOSE(FILTER(Sheet1!B1:1,Sheet1!B1:1<>""))));MMULT(ARRAYFORMULA(OFFSET(Sheet1!B2,,,COUNTA(Sheet1!A2:A),COUNTA(Sheet1!B1:1))*1),ArrayFormula(--(TRANSPOSE(UNIQUE(TRANSPOSE(FILTER(Sheet1!B1:1,Sheet1!B1:1<>""))))=TRANSPOSE(FILTER(Sheet1!B1:1,Sheet1!B1:1<>"")))))}}
I've made a Sample sheet with solution and explanation.