sum the value of a some cells in column based on multiple conditions using google sheets query

1
votes

I am using google sheet query and subquery to get the sum of cells in column (Col3) if two conditions occur together:

  1. there are cells in (Col4) have the same values
  2. the cell in front of (Col3)and(Col4) cells in (Col5) have the same value.

so far I used a query to select the data and I am trying to use a subquery to sum the wanted value

as shown in the screenshot:

[Screenshot

1
google-sheetsgoogle-sheets-query
@player0 No, i am trying to sum the value of C38 (8300) in column c (which is Col3 in the formula) and any other cell in that column, because : 1) there is a value in column D (which is Col4 in the formula) that have the same value , and 2) the cell in front of them G83 & G84 have the same values (3959), some columns are hidden because it's containeing personal information. – Walid A.Ali
so, you trying to sum C83 + C84? and also D83 + D84? – player0

1 Answers

0
votes

instead A79:H86 you will put your query from your image, then adjust G79:G86 to fit the virtual array construct and also adjust {2,9} add more columns (like those hidden ones) and then, of course, adjust Cols if you added more stuff in {2,9}

=ARRAYFORMULA(QUERY({IFERROR(VLOOKUP(QUERY(QUERY(
 {A79:H86},"select Col7, sum(Col3) group by Col7 label sum(Col3)''",0),
 "select Col1",0),{G79:G86,A79:H86},{2,9},0), ),
 QUERY({A79:H86},"select Col7, sum(Col3) group by Col7 label sum(Col3)''",0),
 QUERY({A79:H86},"select sum(Col4) group by Col7 label sum(Col4)''",0)},
 "select Col1, Col4, Col5, Col3, Col2 order by Col2",0))

to sum only Col3:

=ARRAYFORMULA(QUERY({IFERROR(VLOOKUP(QUERY(QUERY(
 {A79:H86},"select Col7, sum(Col3) group by Col7 label sum(Col3)''",0),
 "select Col1",0),{G79:G86,A79:H86},{2,9},0), ),
 QUERY({A79:H86},"select Col7, sum(Col3) group by Col7 label sum(Col3)''",0)},
 "select Col1, Col4, Col3, Col2 order by Col2",0))