using sumifs and find the value of 1000

[sguna1982]

Dear team,

Need your urgent help.

ID	DATE	AMOUNT	Expected result	Remarks
AAAAAA	01-Jan-21​	100​	100​	if <1000 take the actual value basis date and ID
AAAAAA	01-Jan-21​	500​	500​	if <1000 take the actual value basis date and ID
AAAAAA	01-Jan-21​	100​	100​	if <1000 take the actual value basis date and ID
AAAAAA	01-Jan-21​	0​	0​	if <1000 take the actual value basis date and ID
AAAAAA	01-Jan-21​	400​	300​	If crossed 1000 basis date and ID, 1000-sum of previous entries
BBBBBB	02-Jan-21​	10001​	1000​	If >1000 also freeze 1000 and remaining value should be for all the next following entries for same date and ID
BBBBBB	02-Jan-21​	10​	0​	If >1000 also freeze 1000 and remaining value should be for all the next following entries for same date and ID
BBBBBB	02-Jan-21​	0​	0​	If >1000 also freeze 1000 and remaining value should be for all the next following entries for same date and ID
BBBBBB	02-Jan-21​	1111​	0​	If >1000 also freeze 1000 and remaining value should be for all the next following entries for same date and ID
BBBBBB	02-Jan-21​	234​	0​	If >1000 also freeze 1000 and remaining value should be for all the next following entries for same date and ID

 

[herofox]

PLEASE Uploade the Excel File !!!!

 

[sguna1982]

Excel file attached

 

[bosco_yip]

In D2, formula copied down :

=IF(SUMIF(A$2:A2,A2,C$2:C2)>1000,1000-SUMIF(A$1:A1,A2,D$1:D1),C2)

 

[sguna1982]

In D2, formula copied down :

=IF(SUMIF(A$2:A2,A2,C$2:C2)>1000,1000-SUMIF(A$1:A1,A2,D$1:D1),C2)

View attachment 73621

Dear bosco,

Thanks for immediate reply.

Need more help.

If I use sumifs and add more criteria its not working perfectly. excel attached for your reference.

Thanks in advance.
Guna

 

[bosco_yip]

In F2, changed your formula :

From this >>

=IF(SUMIFS(C$2:C2,A$2:A2,A2,B$2:B2,B2)>1000,1000-SUMIFS(D$1:D1,A$1:A1,A2,B$1:B1,B2),C2)

To this >>

=IF(SUMIFS(C$2:C2,A$2:A2,A2,B$2:B2,B2)>1000,1000-SUMIFS(F$1:F1,A$1:A1,A2,B$1:B1,B2),C2)

and copied down

Regards