SUMIFS,其中"sum_range"位于列未知 [英] SUMIFS where the "sum_range" column is unknown
问题描述
我在Sheet1上有一堆数据
我在Sheet2上有一个SUMIFS
公式
我正在汇总的列名为NETPERD1
,NETPERD2
,NETPERD3
..... NETPERD12
I have a pile of data on Sheet1
I have a SUMIFS
formula on Sheet2
The columns I am summing are named NETPERD1
, NETPERD2
, NETPERD3
.....NETPERD12
我想写SUMIFS
,以便可以轻松更改要累加的列.
在Sheet2的单元格$C$4
中,我将输入NETPERD1
或NETPERD2
等,并且我希望我的SUMIF
确定应加总的列.
我想我应该可以使用IndexMatch
来做到这一点,但我无法使其正常工作.
I want to write the SUMIFS
so that I can easily change which column I am summing.
On Sheet2, in cell $C$4
, I will enter NETPERD1
or NETPERD2
etc. and I want my SUMIF
to determine which column it should sum.
I think I should be able to do this with IndexMatch
but I can't get it to work.
这是我的SUMIFS
正常工作.
我想用$C$4
Here is my SUMIFS
that works.
I want to replace NETPERD1
with $C$4
=SUMIFS(Sheet1!NETPERD1,Sheet1!ACCTGRPCOD,Perplas!$A15,Sheet1!AUDTORG,Perplas!C$1,Sheet1!FSCSDSG,Perplas!C$2,Sheet1!FSCSYR,Perplas!C$3)
推荐答案
如果命名范围跨越整列,则对于INDEX/MATCH,请尝试:
If your named ranges span the entire column, then for the INDEX/MATCH try:
=SUMIFS(INDEX(Sheet1!$A:$Z,,Match(Perplas!C$4,Sheet1!A$1:Z$1,0)),...
将Z
更改为最右边的列.
这篇关于SUMIFS,其中"sum_range"位于列未知的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!