使用“间接”功能在“SUMPRODUCT” True / False Cell Reference的公式 [英] Using "INDIRECT" function in the "SUMPRODUCT" formula for True/False Cell Reference
问题描述
这是我目前使用的公式:
= SUMPRODUCT((INDIRECT(A2):INDIRECT A&(ROW() - 1))= A359)* 1)
很好,但是我想使用它:
= SUMPRODUCT((INDIRECT(A2):INDIRECT &(ROW() - 1))= INDIRECT(A&(ROW())))* 1)
不幸的是,我得到一个 #VALUE!
。我究竟做错了什么? INDIRECT(A& ROW(()))
和 A359
返回相同的值,我不知道为什么这不行。
我没有使用简单的 COUNTIF
函数的原因是因为我剥离了所有不必要的组件的公式,只剩下我遇到麻烦的部分(即我需要使用 SUMPRODUCT
公式和一个 COUNTIF
我不知道为什么你需要 INDIRECT
而不是普通的单元格引用,但这里的具体问题是 ROW
函数返回一个数组,即使它返回单个值,例如它返回{x}而不是x - 在某些情况下,Excel无法处理。
尝试将第二个一个
函数 - 该值不会更改,但它会摆脱数组,即 SUM
函数中的ROW
= SUMPRODUCT((INDIRECT(A2):INDIRECT(A&(ROW() - 1))= INDIRECT(A& SUM ())))* 1)
这将消除#VALUE!在您的公式不变的基本结构不变
的同时发生错误
This is the formula that I am currently using:
=SUMPRODUCT((INDIRECT("A2"):INDIRECT("A"&(ROW()-1))=A359)*1)
It works great, but I would like to use this instead:
=SUMPRODUCT((INDIRECT("A2"):INDIRECT("A"&(ROW()-1))=INDIRECT("A"&(ROW())))*1)
Unfortunately I get a #VALUE!
. What am I doing wrong? Both INDIRECT("A"&ROW(()))
and A359
return the same value, so I'm not sure why this won't work.
The reason I am not using a simple COUNTIF
function is because I stripped my formula of all unnecessary components and only left the part that I am having trouble with (i.e. I need to use the SUMPRODUCT
formula and a COUNTIF
formula will not work)
Thanks in advance!
I'm not sure why you need INDIRECT
instead of ordinary cell references but the specific problem here is that ROW
function returns an "array", even when it returns a single value, e.g. it returns {"x"} rather than just "x" - and in some circumstances Excel can't process that.
Try wrapping the second ROW
function in a SUM
function - the value doesn't change but it gets rid of the array, i.e.
=SUMPRODUCT((INDIRECT("A2"):INDIRECT("A"&(ROW()-1))=INDIRECT("A"&SUM(ROW())))*1)
This will eliminate #VALUE! eror while leaving the essential structure of your formula unchanged
这篇关于使用“间接”功能在“SUMPRODUCT” True / False Cell Reference的公式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!