excel

Subtotal of column value specified in another cell

I have this table Table that i need to do a subtotal("E") of the column with the value I specified in "H1", so I don't have to manually change the range

=SUBTOTAL(109;$D$2:D2)

In the example, I put 30 in "H1" so I need to find the "30" column and do a subtotal I tried messing with match and index without success

Solution

  • Try this:

    =SUM(OFFSET($A$2:A2,,MATCH($H$1,$1:$1,0)-1))

    Following the comment by BigBen, here an alternative version without using the OFFSET function:

    =SUM(INDIRECT("R"&ROW($E$2)&"C"&MATCH($H$1,$1:$1,0)&":R"&ROW(E2)&"C"&MATCH($H$1,$1:$1,0),FALSE))