Is there an easy method for turning a some binary columns into rows based on the value = 1, and then aggregating the total of the aggregated fields.
Example:
| Condition_1 | Condition_2 | Condition_3 | agg_1 | agg_2 |
|---|---|---|---|---|
| 0 | 1 | 1 | 50 | 100 |
| 1 | 1 | 1 | 50 | 100 |
| 0 | 0 | 1 | 50 | 100 |
| 0 | 1 | 0 | 50 | 100 |
And have is set to something like:
| Cohort | agg_1 | Agg_2 |
|---|---|---|
| Condition_1 | 50 | 100 |
| Condition_2 | 150 | 300 |
| Condition_3 | 150 | 300 |
CodePudding user response：
Try this:
Sample data:
In cell A8 insert this formula to populate the column names:
={"Cohort", D1:E1}In cell A9 insert this formula to populate the row names:
=transpose(A1:C1)In cell B9 insert this formula to sum the aggregated fields and drag it to B11:
=query(query({index($A$2:$C$5,,match(A9, $A$1:$C$1)),$D$2:$E$5}, "select sum(Col2), sum(Col3) where Col1 = 1", 0), "offset 1", 0)
In the index section of the formula above, it will create a table which consists of the target condition column agg_1 and agg_2 column:
Example:
then using the Query function, it will sum the columns Agg_1 and Agg_2s based on the value of the Condition column.