admin管理员组

文章数量:1384579

I have this 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

I have this 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

  • excel
Share Improve this question edited Mar 17 at 20:22 BigBen 50.2k7 gold badges28 silver badges44 bronze badges asked Mar 17 at 20:20 Shawn DjontzShawn Djontz 51 silver badge3 bronze badges 1
  • abount SCAN() with INDEX/MATCH like =SCAN(0,INDEX(B2:D12,,MATCH(H1,B1:D1,0)),SUM)? – Harun24hr Commented Mar 18 at 2:10
Add a comment  | 

1 Answer 1

Reset to default 0

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))

本文标签： excelSubtotal of column value specified in another cellStack Overflow

版权声明：本文标题：excel - Subtotal of column value specified in another cell - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1744536894a2611369.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。