SUMIF动态更改求和列 [英] SUMIF dynamically change summing column

问题描述

我正在使用SUMIFS，并希望 sum_range 动态地根据列名更改.

I am using SUMIFS and want the sum_range dynamically to change according to the name I have of a column.

我有一张约有100列的表格.说这些列之一是Paid_BC_items.我想要一个公式来查找Paid_BC_items所在的列，并以某种方式将其插入到Sheet4!J:J部分所在的SUMIF中.我这里还有其他一些固定的标准，因此它们不需要动态.

I have a table with about 100 columns. Say one of these columns is Paid_BC_items. I want a formula that looks for which column Paid_BC_items is in and somehow insert that into the SUMIF here where the Sheet4!J:J part is. I have a few other criteria here too which are fixed so they don't need to be dynamic.

=SUMIFS(Sheet4!J:J,Sheet4!$C:$C,Sheet2!$D$3,Sheet4!$E:$E, Sheet2!$C6, Sheet4!$G:$G, Sheet2!$D6)

例如，如果我将列标题更改为其他名称，我想要SUMIF，然后在大表中查找该列并返回.

If for example I changed the column heading to something else I want the SUMIF then to look for that column in the big tables and return that.

我知道它与索引，匹配和间接关系有关，但我现在无法弄清楚.

I know it has something to do with indexing, matching and indirects but I just can't figure it out right now.

Year    Week    Total Orders    Paid_BC_items   Free_BC_items
2014    1   971     147 104
2014    2   1565    339 213
2014    3   1289    391 209
2014    4   1171    389 228
2014    5   1163    375 240
2014    6   1298    405 330
2014    7   1233    404 292

推荐答案

尝试使用它代替总和范围

Try using this in place of the sum range

INDEX(Sheet4!A:DZ,0,MATCH("Paid_BC_Items",A1:DZ1,0))

当您将INDEX和0用作行参数时，您将获得整列....并且MATCH根据标题选择正确的列

when you use INDEX with 0 as the row argument you get the whole column....and MATCH picks the right column based on the header

整个公式变为:

=SUMIFS(INDEX(Sheet4!A:DZ,0,MATCH("Paid_BC_Items",A1:DZ1,0)),Sheet4!$C:$C,Sheet2!$D$3,Sheet4!$E:$E, Sheet2!$C6, Sheet4!$G:$G, Sheet2!$D6)

这篇关于SUMIF动态更改求和列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！