How do i fix the value of sumifs in Google sheets when i add more rows in the bottom?-CodePudding

I would need the amount available for each code based on the country and maximum available limit is 12400. But when i try to execute it the values in D3, D6 and D7 are changing as we keep on adding the rows but this is how i basically need it D3 should be 11,166 (12400-1234) D6 should be 4623 (11,166-6543) D7 should be -60,809 (4623-65432)

Can you please help me with a formula which would make my life easy

CodePudding user response：

Try this formula out in cell D3 of your sample data

=MAP(A3:A,B3:B,LAMBDA(ax,bx,IF(ax="",,12400-SUM(FILTER(C3:C,A3:A=ax,B3:B=bx,ROW(A3:A)<=ROW(ax))))))

CodePudding user response：

Use BYROW() to iterate each row of Code column. Try-

=BYROW(A3:INDEX(A3:A,COUNTA(A3:A)),LAMBDA(x,12400-SUMIFS(C3:C,A3:A,x,B3:B,"India")))

Here A3:INDEX(A3:A,COUNTA(A3:A)) will return a array of values as well cell reference from A3 to last non empty cell in column A (Assume you do not have any blank rows inside data). If you have blank row, then you have to use different approach. See this