Dynamic Accumulated Calculation for Column D Based on Names and Values in Google Sheets - Stack Overflow

admin管理员组

文章数量:1124759

Sample Output

Column B: NAMES Column C: LEND Column D: RETURN Column E: BALANCE

Can someone help me to write a GoogleSheets formula for column E.

The formula should dynamically calculate the accumulated result for each row based on:

• The continuity of the same name in column B.
• Resetting the calculation for new names in column B.

If it’s the first occurrence of a name in column B, column E is calculated as: Column E = Column C - Column D

For rows where the name in column B matches a previous occurrence, column E is calculated as: Column E = Last 'E' value for the same name + Column C - Column D

When a new name appears in column B, the calculation resets for that name, and column E is again calculated as: Column E = Column C - Column D

I tried various ways, but nothing is giving the required result

Sample Output

Column B: NAMES Column C: LEND Column D: RETURN Column E: BALANCE

Can someone help me to write a GoogleSheets formula for column E.

The formula should dynamically calculate the accumulated result for each row based on:

• The continuity of the same name in column B.
• Resetting the calculation for new names in column B.

If it’s the first occurrence of a name in column B, column E is calculated as: Column E = Column C - Column D

For rows where the name in column B matches a previous occurrence, column E is calculated as: Column E = Last 'E' value for the same name + Column C - Column D

When a new name appears in column B, the calculation resets for that name, and column E is again calculated as: Column E = Column C - Column D

I tried various ways, but nothing is giving the required result

• google-sheets
• google-sheets-formula

Share Improve this question Follow edited 2 days ago Patsytalk 73011 gold badge11 silver badge1414 bronze badges asked 2 days ago Milen NinanMilen Ninan 111 bronze badge New contributor Milen Ninan is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct. 1

• 1 Make sure to provide input and expected output as plain text table in the question. Check my answer or other options to create a table easily, which are easy to copy/paste. Avoid sharing links like spreadsheets, which make the question useless for others or images, which are hard to copy. Also, note that your email address can also be accessed by the public, if you share Google files. – TheMaster Commented 2 days ago

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

Try this formula:

=LET(x, FILTER($B$1:INDIRECT(CONCAT("E",ROW(E2))), $B$1:INDIRECT(CONCAT("B",ROW(E2))) = B2), y, SUM(CHOOSECOLS(x,2)),z,SUM(CHOOSECOLS(x,3)), y-z)

This formula needs to be dragged down, but it can be used in a Byrow for Dynamic Spilling. With your data I assume the Name is in Col B and progresses accordingly. This uses a filter to create a tally of every entry and calculates the balances.

Sample Output

NAME	LEND	RETURN	BALANCE
PERSON A	10		10
PERSON A		100	-90
PERSON A	90		0
PERSON A	60		60
PERSON A	10		70
PERSON A	15		85
PERSON A		24	61
PERSON A		20	41
PERSON A		65	-24
PERSON A	25		1
PERSON A	15		16
PERSON A	70		86
PERSON A	266		352
PERSON B		80	-80
PERSON B	15		-65
PERSON B	65		0
PERSON B		15	-15
PERSON B	352		337
PERSON C	1200		1200
PERSON C		700	500
PERSON D	300		300

References:

Filter

You may try:

=map(B2:B,C2:C,D2:D,lambda(Σ,Λ,Δ,if(Σ="",,sumproduct(B2:Σ=Σ,C2:Λ-D2:Δ))))

本文标签： Dynamic Accumulated Calculation for Column D Based on Names and Values in Google SheetsStack Overflow