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

Question

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

score 0 · Accepted Answer · answered Dec 18 '22 at 17:03

0

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))))))

-

answered Dec 18 '22 at 17:03

rockinfreakshow

15,077
3
12
19

Thank you so much!! this is of great help :) – Neethu Singuluri Dec 20 '22 at 17:33

score 0 · Answer 2 · answered Dec 19 '22 at 02:48

0

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 post by @TheMaster
Then LAMBDA() will apply to SUMIFS() function for each cell of A column.

answered Dec 19 '22 at 02:48

Harun24hr

30,391
4
21
36

Thank you so much!! this is of great help :) – Neethu Singuluri Dec 20 '22 at 17:33
@NeethuSinguluri If you find the answer helpful, then please accept the answer (tick mark it) so that other visitor can know this is solved. – Harun24hr Dec 21 '22 at 02:11

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

2 Answers2