Make a sum of from column whoes values added or redused as per parent sources sheet

Chaturvedi, Santosh 400 Reputation points
2026-09-14T08:10:36.1866667+00:00

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.

User's image

The B19 is formulated to get teh data from parent sheet with below formulation.

User's image

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
0 comments

2 answers

Sort by: Oldest
Most helpful Newest Oldest

  1. 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.

    Was this answer helpful?

  2. Chaturvedi, Santosh 400 Reputation points
    2026-09-14T08:13:39.5633333+00:00

    If i put

    =SUM(C19#)

    Following error comes--

    User's image

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.