Search code examples
google-sheetsformula

Using arrayformula to automate sumif in each row, Google spresdsheet

i want to calculate each value of column named "indikator" using sumif with indirect inside. here the formula: =sumif(indirect("'"&xlookup(B2,Rekap!$C$3:$C$28,Rekap!$A$3:$A$28)&". "&B2&"'!"&"c4:c"),C2,indirect("'"&xlookup(B2,Rekap!$C$3:$C$28,Rekap!$A$3:$A$28)&". "&B2&"'!"&"h4:h"))

screenshoot

i was expect like value in "expected". the formula above is work, but doesn't when i add arrayfomula, like this: =ARRAYFORMULA(sumif(indirect("'"&xlookup(B2:B,Rekap!$C$3:$C$28,Rekap!$A$3:$A$28)&". "&B2:B&"'!"&"c4:c"),C2:C,indirect("'"&xlookup(B2:B,Rekap!$C$3:$C$28,Rekap!$A$3:$A$28)&". "&B2:B&"'!"&"h4:h")))

are there any solution?

Solution

  • You may try with:

    =map(B2:B,C2:C,lambda(b,c,if(or(b="",c=""),,sumif(indirect(xlookup(b,Rekap!C3:C28,Rekap!A3:A28)&". "&b&"!C:C"),c,indirect(xlookup(b,Rekap!C3:C28,Rekap!A3:A28)&". "&b&"!H:H")))))