Sumproduct公式的疑问

purpurin

各位好，我经常使用sumproduct完成多条件求和公式，但是今天遇到一个尝试了多次都不成功，不知道问题出在哪里。



如上为原始数据，原始数据中，不是每一列都会有基础金额
我要求和的条件为：1.姓名；2.状态为“已经下单”或者“部分下单”；3.基础金额不为0，4.差额需要大于0
最终求对应人名下差额的和。

原始数据中差额部分我写了IF公式，=IF(D-C<0,0,D-C)
SUMPRODUCT的公式我写的是
=SUMPRODUCT((A1:A50="张三“)*(B1:B50={"已经下单","部分下单"})*(D1:D50<>"")*E1:E50)

返回后有数字，但是金额是不对的。我不确定是不是因为最终求和的部分为有公式的差额导致的？因为行会不断增加，订单金额也有可能调整。所以E列差额必须保持有公式随时计算。有没有什么解决办法？非常感谢

purpurin

最下面俩个图请大家不要看，我怎么也删除不掉。。。就看第一个就好了

温馨豆

我觉得问题应该在中间那个数组上

purpurin

温馨豆 发表于 2018-1-18 15:41
我觉得问题应该在中间那个数组上

因为我常用sumproduct，公式写的也非常多，都没出现任何问题。

我个人觉得问题出在最后求和的差额原始数据上，因为差额部分在原始数据中是有公式的，而且差额>0也是条件之一。但是我不知道怎么解决

温馨豆

purpurin 发表于 2018-1-18 15:47
因为我常用sumproduct，公式写的也非常多，都没出现任何问题。

我个人觉得问题出在最后求和的差额原始 ...

得到什么结果？

jiangxiaoyun

用这函数还不如用2个sumifs，"已经下单","部分下单"。
或者在sumifs前面套个sum对{"已经下单","部分下单"}这个数组进行求和

温馨豆

我看懂了，你最后*E1那里，乘了一个文本，所以是错误值，从第二行开始就阔以了

温馨豆

第一个方法，保证E列条件都是数值，第二个方法，把E列的区域单独分出来，这个需要每个区域是一样的大小。

1. =SUMPRODUCT((A1:A50="张三")*((B1:B50="已经下单")+(B1:B50="部分下单"))*(D1:D50<>""),E1:E50)

复制代码

如果要拖动，该绝对引用的地方，绝对引用

purpurin

温馨豆 发表于 2018-1-18 16:13
第一个方法，保证E列条件都是数值，第二个方法，把E列的区域单独分出来，这个需要每个区域是一样的大小。
...

谢谢回复，不过我不太理解，E列怎么可能是文本么？在原始数据中我设置了公式才会计算出E列的数值，如果是文本格式的话，公式根本是无法计算的

purpurin

温馨豆 发表于 2018-1-18 16:13
第一个方法，保证E列条件都是数值，第二个方法，把E列的区域单独分出来，这个需要每个区域是一样的大小。
...

=SUMPRODUCT((A1:A50="张三")*((B1:B50="已经下单")+(B1:B50="部分下单"))*(D1:D50<>""),E1:E50)

另外上面的这个中间的部分，分开+和我列出{}组合加法我都曾经测试过，但是返回结果依然是错误的数据。

返回的结果不是N/A之类的错误标识，而是一个错误的求和值，比如应该返回25，但是返回了35之类的。