I was working on a summary sheet recording different campaigns with its title, nsu, impression, and clicks. However, I have like 10+ columns for each platform. On the left side, I wanted to be able to create a summary where I could see the sum of the data for each campaign. So if lets say Mainstream title exists on each column, I wanted to see the sum data of it. But also, unique titles who have no similarity to other columns would also be listed into the summary.
Here’s a sample sheet of my data.
I’ve tried to use the following formula but aside from the fact that it lead me to know here, It’s obviously too time consuming. Can anyone recommend anything to do with this?
=QUERY({
FLATTEN({E2:E, I2:I, M2:M, Q2:Q, U2:U, Y2:Y, AC2:AC, AG2:AG, AK2:AK, AO2:AO, AS:AS, AW:AW, BA:BA}),
FLATTEN({F2:F, J2:J, N2:N, R2:R, V2:V, Z2:Z, AD2:AD, AH2:AH, AL2:AL, AP2:AP, AT2:AT, AX2:AX, BB2:BB}),
FLATTEN({G2:G, K2:K, O2:O, S2:S, W2:W, AA2:AA, AE2:AE, AI2:AI, AM2:AM, AQ2:AQ, AU2:AU, AY2:AY, BC2:BC}),
FLATTEN({H2:H, L2:L, P2:P, T2:T, X2:X, AB2:AB, AF2:AF, AJ2:AJ, AN2:AN, AR2:AR, AV2:AV, AZ2:AZ, BD2:BD})
}, "SELECT Col1, SUM(Col2), SUM(Col3), SUM(Col4) WHERE Col1 IS NOT NULL GROUP BY Col1", 1)
You need nested FILTER() then sum. I have used MAKEARRAY() formula to make it dynamic spill array formula. Try-
=LET(x,UNIQUE(TOCOL(E3:E,1)),
HSTACK(x,MAKEARRAY(ROWS(x),3,LAMBDA(r,c,SUM(FILTER(FILTER(F3:BH,F2:BH2=INDEX(B2:D2,1,c)),E3:E=INDEX(x,r,1)))))))
2
Here’s one approach you may test out:
=query(
let(Λ,tocol(,1),reduce(Λ,sequence(14,1,1,4),lambda(a,c,vstack(a,let(Σ,choosecols(E3:BH,sequence(4,1,c)),ifna(filter(Σ,index(Σ,,1)<>""),Λ)))))),
"select Col1, sum(Col2), sum(Col3), sum(Col4) group by Col1 label sum(Col2) '',sum(Col3) '',sum(Col4) ''")
4