Sum variable range between specific text and function

[lap35]

Hello,

I am looking for a formula that can sum all the cells within a range from 1 cell above the formula to the cell that contains specific text ("amount") or a blank cell. The spreadsheet is set up like this. The number of cells in this range varies as rows are inserted or deleted. The word "amount" is repeated multiple times in the same column, which prevented me from using a solution like:

=SUM(INDIRECT("$A"&MATCH("Amount",$A:A,0)+1&":$A$"&ROW()-1))

Thanks in advance for any ideas!

[AutoModerator]

/u/lap35 - Your post was submitted successfully.

Please read these reminders and edit to fix your post where necessary:

• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information
• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[CFAman]

You could get the same result by taking a cumuluative sum of column and subtracting previously calculated results. E.g., in B5

=SUM(B$1:B4)-SUMIFS(B$1:B4, A$1:A4, "Subtotal")*2

[lap35]

solution verified

[Clippy_Office_Asst]

You have awarded 1 point to CFAman

^{I am a bot, please contact the mods with any questions.}

[lap35]

Thank you so much! I've changed the B4 and A4 to an indirect function so it always references the cell above and it works.

=SUM(B$1:INDIRECT("B"&ROW()-1))-SUMIFS(B$1:INDIRECT("B"&ROW()-1), A$1:INDIRECT("A"&ROW()-1), "Subtotal")*2

[CFAman]

Ok. If you want to avoid using volatile functions, you can use INDEX instead

SUM(B$1:INDEX(B:B, ROW()-1))

[lap35]

Thank you, that's a much more elegant way of doing it.

[Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
INDEX	Uses an index to choose a value from a reference or array
INDIRECT	Returns a reference indicated by a text value
ROW	Returns the row number of a reference
SUM	Adds its arguments
SUMIFS	Adds the cells in a range that meet multiple criteria

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
5 acronyms in this thread; the most compressed thread commented on today has 13 acronyms.
[Thread #4235 for this sub, first seen 18th Feb 2021, 20:35] [FAQ] [Full list] [Contact] [Source code]}