Microsoft 365 and Office | Excel | Other | Other
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
If i put
=SUM(C19#)
Following error comes--
Make a sum of from column whoes values added or redused as per parent sources sheet
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(51.2, 14.000000000000002%, 14%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ECS%3C/text%3E%3C/svg%3E)
Chaturvedi, Santosh
400
Reputation points
Hello
Please see the case below.
Datas are not real
I want tto take teh sum in E9 from C19 to ...C307) --> The data will added or reduced in column C. see below.
The B19 is formulated to get teh data from parent sheet with below formulation.
Requirement :
How to make formula in E9 to takes all values in column C which will change constatnly from the paraent sheet
Microsoft 365 and Office | Excel | Other | Other
2 answers
Sort by: Newest
-
Chaturvedi, Santosh 400 Reputation points
2026-09-14T08:13:39.5633333+00:00 -
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(252.8, 79%, 34%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ERA%3C/text%3E%3C/svg%3E)
Rory Archibald 19,055 Reputation points Volunteer Moderator2026-09-14T10:04:06.9166667+00:00 If C19 does not contain a dynamic array formula, using C19# will produce a #REF! error. Usually the simplest method is to apply a larger range and use the trimrange dot operator - e.g. C19:.C5000
Note the dot after the colon.
Sign in to comment -
-
AI answer
2026-09-14T08:10:53.3966667+00:00 Use a dynamic sum that points to the spilled range starting at C19.
In E9, enter:
=SUM(C19#)This sums the entire spilled array that starts in C19, so when the parent-sheet formula adds or removes rows, the total updates automatically.
If the values in column C are generated by a dynamic array formula, this is the correct approach because Excel automatically resizes the
C19#spill range.If needed, a standard fixed-range version is:
=SUM(C19:C307)But that does not adjust automatically when the number of returned rows changes.
If the requirement is to total only values that meet conditions, Excel also supports SUMIF for one condition and SUMIFS for multiple conditions.
AI-generated content may be incorrect. Read our transparency notes for more information.