CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
HSHO
HSHO

Reputation: 537

calculate the sum of columns individually based on each insurance category

I have below query in Sheet "Report" which is extracting the data from Sheet "Data" based on 2 filters availble in Sheet "Report" Cell B2 and C2 and this Query is working fine.

I would like to introduce an additional condition in the Query. This condition is based on Column D from the "Data" sheet. The Query will calculate the sum of columns individually from G2 to BB, based on each insurance category. It will then add a new row at the top, displaying the name of the insurance and the sum of its corresponding rows.

I have added a 3rd Sheet based on the desired result.

your help will be much appreciated in this regards.

Spreadsheet Link

=QUERY(ArrayFormula(Data!A4:BB), "SELECT D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z, AA, AB, AC, AD WHERE B = '"&B2&"' AND C = '"&C2&"' ORDER BY D ASC", 0)

then did this and still not working Error Unable to parse query string for Function QUERY parameter 2: CANNOT_GROUP_WITHOUT_AGG

=QUERY(ArrayFormula(Data!A4:BB), "SELECT D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z, AA, AB, AC, AD WHERE B = '"&B2&"' AND C = '"&C2&"' GROUP BY D ORDER BY D ASC LABEL SUM(G) 'Sum of G', SUM(H) 'Sum of H', SUM(I) 'Sum of I', SUM(J) 'Sum of J', SUM(K) 'Sum of K', SUM(L) 'Sum of L', SUM(M) 'Sum of M', SUM(N) 'Sum of N', SUM(O) 'Sum of O', SUM(P) 'Sum of P', SUM(Q) 'Sum of Q', SUM(R) 'Sum of R', SUM(S) 'Sum of S', SUM(T) 'Sum of T', SUM(U) 'Sum of U', SUM(V) 'Sum of V', SUM(W) 'Sum of W', SUM(X) 'Sum of X', SUM(Y) 'Sum of Y', SUM(Z) 'Sum of Z', SUM(AA) 'Sum of AA', SUM(AB) 'Sum of AB', SUM(AC) 'Sum of AC', SUM(AD)", 1)

Upvotes: 0

Views: 46

Answers (1)

rockinfreakshow
rockinfreakshow

Reputation: 30281

Added one solution here for you to test out:

=let(data_,sort(filter(Data!D:AD,xmatch(Data!B:B,if(B2="ALL",Data!B4:B,B2)),xmatch(Data!C:C,if(C2="ALL",Data!C4:C,C2))),1,1),
     Σ,choosecols(data_,sequence(25,1,3)),Λ,choosecols(data_,2),Δ,choosecols(data_,1),
     Γ,reduce(wraprows(,columns({Λ,Σ}),),unique(sort(tocol(Δ,1),1,1)),lambda(a,c,{a;{c,bycol(filter(Σ,Δ=c),lambda(z_,sum(z_)))};filter({Λ,Σ},Δ=c)})),
     filter(Γ,index(Γ,,1)<>""))

enter image description here

Upvotes: 2

Related Questions