替代产品 [英] Sumproduct with Substitute
问题描述
10 * 5 7 9 25 * 10
当我做一个SUM(A1:A8)时,我得到5 + 7 + 9 + 10 = 31的结果,所需的输出。
现在,我还要求总和,而不管*后缀。我试图通过使用
SUMPRODUCT(SUBSTITUTE(A1:A8,*,))
它适用于
code> SUMPRODUCT({10,5,7,,9,,25,10})
输出0,因为都是文本值。当我使用
SUMPRODUCT(值(SUBSTITUTE(A1:A8,*,)))
它适用于
SUMPRODUCT({10,5,7,#VALUE!,9,#VALUE!,25,10})
,最后输出#Value!有人可以帮我解决吗?谢谢你的时间。
我假设空格是问题,因为当你使用= VALUE()一个错误。尝试将零连接到SUBSTITUTE的开头,使空白变为零
= SUMPRODUCT(VALUE(0& SUBSTITUTE(A1:A8, ,))))
或者您可以使用+0代替VALUE,即
= SUMPRODUCT((0& SUBSTITUTE(A1:A8,*,))+ 0)
I have a range, containing numeric values & blank cells. Some of the numeric values will have * as suffix.
10* 5 7 9 25* 10
When I do a SUM(A1:A8), I get result of 5+7+9+10 = 31, the required output. Now, I also require the total sum irrespective of * suffix. I'm trying to solve by using
SUMPRODUCT(SUBSTITUTE(A1:A8,"*",""))
It works out to
SUMPRODUCT({"10","5","7","","9","","25","10"})
And outputs 0 since all are text values. When I use
SUMPRODUCT(value(SUBSTITUTE(A1:A8,"*","")))
It works out to
SUMPRODUCT({10,5,7,#VALUE!,9,#VALUE!,25,10})
and finally outputs #Value!. Can someone help me to solve this? Thanks for your time.
I assume that the blanks are the problem because when you use =VALUE("") you get an error. Try concatenating a zero to the start of SUBSTITUTE so that blanks become zeroes
=SUMPRODUCT(VALUE(0&SUBSTITUTE(A1:A8,"*","")))
or you can use +0 in place of VALUE, i.e.
=SUMPRODUCT((0&SUBSTITUTE(A1:A8,"*",""))+0)
这篇关于替代产品的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!