[已解决]多条件求和

Eric_f765

老师，请教多条件求和的问题，详见附件，谢谢。

最佳答案

月排行榜 / 总排行榜

孙明达

2017-2-18 13:55

sumif写法：因为D列编码超过15位了，excel支持的数字最大精度为15位，超过15位以外的数字实际上都为0 了，所以要加*强制为文本型，*这里是通配符

1. =SUMIF(BOM!$C:$C,'E-handbook BOM'!D15&"*",BOM!D:D)

复制代码

sumifs的用法和sumif差不多的，只不过是第一参数是求和区域，这个用法看看帮助或百度下就很容易理解了。

1. =SUMIFS(BOM!D:D,BOM!C:C,'E-handbook BOM'!D15&"*")

复制代码

sumproduct出错的原因是，两个表中B列的数据类型不一样，明细表是文本型，汇总表中的是常规类型，A列也要注意，这样用--把文本型转换为常规数字

1. =SUMPRODUCT((BOM!$A$3:$A$80='E-handbook BOM'!A15)*(--BOM!$B$3:$B$80='E-handbook BOM'!B15)*(BOM!$D$3:$D$80))

复制代码

跳转到最佳答案楼层

zyouong

因为D15超过12位了
=SUMIF(BOM!$C:$C,"*"&RIGHT('E-handbook BOM'!D15,16),BOM!D:D)

Eric_f765

zyouong 发表于 2017-2-17 16:04
因为D15超过12位了
=SUMIF(BOM!$C:$C,"*"&RIGHT('E-handbook BOM'!D15,16),BOM!D:D)

老师，那sumifs 怎么写呢？sumproduct 为什么不对，也是15位的关系吗？感谢！

zyouong

多条件求和.rar (17.46 KB, 下载次数: 6)

2017-2-17 16:14 上传

点击文件名下载附件

孙明达

sumif写法：因为D列编码超过15位了，excel支持的数字最大精度为15位，超过15位以外的数字实际上都为0 了，所以要加*强制为文本型，*这里是通配符

1. =SUMIF(BOM!$C:$C,'E-handbook BOM'!D15&"*",BOM!D:D)

复制代码

sumifs的用法和sumif差不多的，只不过是第一参数是求和区域，这个用法看看帮助或百度下就很容易理解了。

1. =SUMIFS(BOM!D:D,BOM!C:C,'E-handbook BOM'!D15&"*")

复制代码

sumproduct出错的原因是，两个表中B列的数据类型不一样，明细表是文本型，汇总表中的是常规类型，A列也要注意，这样用--把文本型转换为常规数字

1. =SUMPRODUCT((BOM!$A$3:$A$80='E-handbook BOM'!A15)*(--BOM!$B$3:$B$80='E-handbook BOM'!B15)*(BOM!$D$3:$D$80))

复制代码

Eric_f765

孙明达 发表于 2017-2-18 13:55
sumif写法：因为D列编码超过15位了，excel支持的数字最大精度为15位，超过15位以外的数字实际上都为0 了， ...

老师，

非常感谢！还有2点麻烦指教：
1，“&*” 是强制把超过15位的数字转化为文本。D列本来就是用&链接的A列和B列，我的理解本来就是文本。为什么还要转化为文本？
2，sumproduct 对格式有要求吗？还是2边的格式一致就可以。如果是文本就都是文本，如果是数字就都是数字，常规。。。？

不知道我表达清楚了没有，再次感谢老师！

孙明达

Eric_f765 发表于 2017-2-19 09:33
老师，

非常感谢！还有2点麻烦指教：

1.这个我也说不清楚，也是学习了老师们的解答后套用的，也是一种套路吧。
2.数据有四种类型，文本型，数值型，错误值，逻辑值，sumproduct的判断条件是用=来判断的，所以=前后的数据类型等都必须完全一致，就像你说的那样，都是文本或都是数字

流浪铁匠

Eric_f765 发表于 2017-2-19 09:33
老师，

非常感谢！还有2点麻烦指教：

关于第1条，countif/sumif这类函数，criteria参数当以"="作为比较运算符或者为纯字符串时，若内容为真数值/文本型数值时会自动识别为数值进行统计，
统计时对于range内的所有真数值/文本型数值等均会被统计在内
当criteria参数内有">","<"等比较运算符时，若内容为真数值/文本型数值时，只统计range内的数值部分，range内的文本型数字将被忽略
此时需要使用 &"*" 方式强制识别为文本时才会对引用区域的文本型数值统计

所以因为你的D15单元格内为纯数字编号，此时sumif会把criteria参数的号码视为数值，而数值的精度只有15位，后面的都看成0了
统计时对于range内的所有真数值/文本型数值等均会被统计在内，也被视为数值
所以造成统计错误
此时&“*”，会把条件识别为文本，此时才能正确对range参数内的编号来条件求和


  

流浪铁匠

针对第2条，sumproduct是数组运算，二者格式相同时判断时才能返回true，才能返回正确结果

Eric_f765

流浪铁匠 发表于 2017-2-20 18:33
关于第1条，countif/sumif这类函数，criteria参数当以"="作为比较运算符或者为纯字符串时，若内容为真数 ...

非常感谢老师的详细解释，谢谢！