Search code examples
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querygs-vlookup

Apply array formula to sumifs

I have Google sheet with sum-ifs functions, but I need to make it apply to the whole columns instead of just one cell and to apply automatically to new rows when there added

I know that arrays don't work with sumifs after doing research but I can't seem to figure out to apply an array function to this.

screen grab of sheet

=ArrayFormula(SUMIFS(K:K,C:C,C2,L:L,false))

so I tried instead to make it a =sum(if(and function instead with an array, but couldn't get that to work either. not sure how to get it to apply to the same affect as the above formula

I need to apply the following sum-ifs all the conditions are met to each cell in the selected column

Solution

  • basically like this:

    =QUERY({C2:C, K2:L}, 
 "select Col1,sum(Col2) 
  where Col1 is not null 
    and Col3 = FALSE 
  group by Col1 
  label sum(Col2)''", 0)

    0

    if you want to pair it with list of names then use VLOOKUP:

    =ARRAYFORMULA(IFERROR(VLOOKUP(C2:C, QUERY({C2:C, K2:L}, 
 "select Col1,sum(Col2) 
  where Col1 is not null 
    and Col3 = FALSE 
  group by Col1 
  label sum(Col2)''", 0), 2, 0)))

    0