How to SUM all values of a Filter with a relative end range

Question

How can I SUM the totals (cell below) of each column titled "$ Balance" between the ranges of C1-HD2?

• The column at the end of the range, HD, needs to be flexible for column additions/removals.

It's possible that HLOOKUP isn't even the right function... I haven't found a way to SUM the HLOOKUP function... which only appears to return a specified cell, (e.g. where E1 is "$Balance"):

=HLOOKUP($E$1, $C$1:HD$2, 2, FALSE)

Any ideas?

Answer 1

try:

=SUM(FILTER($C2:HD2; $C$1:HD$1="$ Balance"))

or:

=SUM(FILTER(
 INDIRECT(ADDRESS(2, 3)&":"&ADDRESS(2, COLUMN()-1)), 
 INDIRECT(ADDRESS(1, 3)&":"&ADDRESS(1, COLUMN()-1))="$ Balance"))

if you want to drag down the 2nd formula change two 2 for ROW(A2)