SUMPRODUCT和SUMIFS公式怎么精简

csj1260901876

=SUMPRODUCT((燃料油!M:M>$J$3)*(燃料油!M:M<=$K$3)*(燃料油!F:F="装")*(燃料油!G:G="液1#B"))+SUMPRODUCT((直馏柴油!M:M>$J$3)*(直馏柴油!M:M<=$K$3)*(直馏柴油!F:F="装")*(直馏柴油!G:G="液1#B"))+SUMPRODUCT((石脑油!M:M>$J$3)*(石脑油!M:M<=$K$3)*(石脑油!F:F="装")*(石脑油!G:G="液1#B"))+SUMPRODUCT((成品汽油!M:M>$J$3)*(成品汽油!M:M<=$K$3)*(成品汽油!F:F="装")*(成品汽油!G:G="液1#B"))+SUMPRODUCT((加氢汽油!M:M>$J$3)*(加氢汽油!M:M<=$K$3)*(加氢汽油!F:F="装")*(加氢汽油!G:G="液1#B"))+SUMPRODUCT((MTBE!M:M>$J$3)*(MTBE!M:M<=$K$3)*(MTBE!F:F="装")*(MTBE!G:G="液1#B"))+SUMPRODUCT((异辛烷!M:M>$J$3)*(异辛烷!M:M<=$K$3)*(异辛烷!F:F="装")*(异辛烷!G:G="液1#B"))+SUMPRODUCT((异戊烷!M:M>$J$3)*(异戊烷!M:M<=$K$3)*(异戊烷!F:F="装")*(异戊烷!G:G="液1#B"))+SUMPRODUCT((工业己烷!M:M>$J$3)*(工业己烷!M:M<=$K$3)*(工业己烷!F:F="装")*(工业己烷!G:G="液1#B"))+SUMPRODUCT((混合二甲苯!M:M>$J$3)*(混合二甲苯!M:M<=$K$3)*(混合二甲苯!F:F="装")*(混合二甲苯!G:G="液1#B"))+SUMPRODUCT((碳8重整!M:M>$J$3)*(碳8重整!M:M<=$K$3)*(碳8重整!F:F="装")*(碳8重整!G:G="液1#B"))+SUMPRODUCT((航煤!M:M>$J$3)*(航煤!M:M<=$K$3)*(航煤!F:F="装")*(航煤!G:G="液1#B"))+SUMPRODUCT((成品柴油!M:M>$J$3)*(成品柴油!M:M<=$K$3)*(成品柴油!F:F="装")*(成品柴油!G:G="液1#B"))+SUMPRODUCT((PX!M:M>$J$3)*(PX!M:M<=$K$3)*(PX!F:F="装")*(PX!G:G="液1#B"))+SUMPRODUCT((苯!M:M>$J$3)*(苯!M:M<=$K$3)*(苯!F:F="装")*(苯!G:G="液1#B"))+SUMPRODUCT((苯乙烯!M:M>$J$3)*(苯乙烯!M:M<=$K$3)*(苯乙烯!F:F="装")*(苯乙烯!G:G="液1#B"))+SUMPRODUCT((乙苯!M:M>$J$3)*(乙苯!M:M<=$K$3)*(乙苯!F:F="装")*(乙苯!G:G="液1#B"))+SUMPRODUCT((一乙二醇!M:M>$J$3)*(一乙二醇!M:M<=$K$3)*(一乙二醇!F:F="装")*(一乙二醇!G:G="液1#B"))+SUMPRODUCT((二乙二醇!M:M>$J$3)*(二乙二醇!M:M<=$K$3)*(二乙二醇!F:F="装")*(二乙二醇!G:G="液1#B"))+SUMPRODUCT((苯酚!M:M>$J$3)*(苯酚!M:M<=$K$3)*(苯酚!F:F="装")*(苯酚!G:G="液1#B"))+SUMPRODUCT((丙酮!M:M>$J$3)*(丙酮!M:M<=$K$3)*(丙酮!F:F="装")*(丙酮!G:G="液1#B"))+SUMPRODUCT((裂解碳九!M:M>$J$3)*(裂解碳九!M:M<=$K$3)*(裂解碳九!F:F="装")*(裂解碳九!G:G="液1#B"))+SUMPRODUCT((乙烯焦油!M:M>$J$3)*(乙烯焦油!M:M<=$K$3)*(乙烯焦油!F:F="装")*(乙烯焦油!G:G="液1#B"))+SUMPRODUCT((液硫!M:M>$J$3)*(液硫!M:M<=$K$3)*(液硫!F:F="装")*(液硫!G:G="液1#B"))+SUMPRODUCT((甲醇!M:M>$J$3)*(甲醇!M:M<=$K$3)*(甲醇!F:F="装")*(甲醇!G:G="液1#B"))+SUMPRODUCT((硫酸!M:M>$J$3)*(硫酸!M:M<=$K$3)*(硫酸!F:F="装")*(硫酸!G:G="液1#B"))+SUMPRODUCT((液碱!M:M>$J$3)*(液碱!M:M<=$K$3)*(液碱!F:F="装")*(液碱!G:G="液1#B"))+SUMPRODUCT((丙烷!M:M>$J$3)*(丙烷!M:M<=$K$3)*(丙烷!F:F="装")*(丙烷!G:G="液1#B"))+SUMPRODUCT((异丁烷!M:M>$J$3)*(异丁烷!M:M<=$K$3)*(异丁烷!F:F="装")*(异丁烷!G:G="液1#B"))+SUMPRODUCT((丁二烯!M:M>$J$3)*(丁二烯!M:M<=$K$3)*(丁二烯!F:F="装")*(丁二烯!G:G="液1#B"))+SUMPRODUCT((裂解碳五!M:M>$J$3)*(裂解碳五!M:M<=$K$3)*(裂解碳五!F:F="装")*(裂解碳五!G:G="液1#B"))+SUMPRODUCT((丙烯!M:M>$J$3)*(丙烯!M:M<=$K$3)*(丙烯!F:F="装")*(丙烯!G:G="液1#B"))



=SUMIFS(燃料油!R:R,燃料油!M:M,">"&$J$3,燃料油!M:M,"<="&$K$3,燃料油!G:G,"=液1#B",燃料油!F:F,"=装")+SUMIFS(直馏柴油!R:R,直馏柴油!M:M,">"&$J$3,直馏柴油!M:M,"<="&$K$3,直馏柴油!G:G,"=液1#B",直馏柴油!F:F,"=装")+SUMIFS(石脑油!R:R,石脑油!M:M,">"&$J$3,石脑油!M:M,"<="&$K$3,石脑油!G:G,"=液1#B",石脑油!F:F,"=装")+SUMIFS(成品汽油!R:R,成品汽油!M:M,">"&$J$3,成品汽油!M:M,"<="&$K$3,成品汽油!G:G,"=液1#B",成品汽油!F:F,"=装")+SUMIFS(加氢汽油!R:R,加氢汽油!M:M,">"&$J$3,加氢汽油!M:M,"<="&$K$3,加氢汽油!G:G,"=液1#B",加氢汽油!F:F,"=装")+SUMIFS(MTBE!R:R,MTBE!M:M,">"&$J$3,MTBE!M:M,"<="&$K$3,MTBE!G:G,"=液1#B",MTBE!F:F,"=装")+SUMIFS(异辛烷!R:R,异辛烷!M:M,">"&$J$3,异辛烷!M:M,"<="&$K$3,异辛烷!G:G,"=液1#B",异辛烷!F:F,"=装")+SUMIFS(异戊烷!R:R,异戊烷!M:M,">"&$J$3,异戊烷!M:M,"<="&$K$3,异戊烷!G:G,"=液1#B",异戊烷!F:F,"=装")+SUMIFS(工业己烷!R:R,工业己烷!M:M,">"&$J$3,工业己烷!M:M,"<="&$K$3,工业己烷!G:G,"=液1#B",工业己烷!F:F,"=装")+SUMIFS(混合二甲苯!R:R,混合二甲苯!M:M,">"&$J$3,混合二甲苯!M:M,"<="&$K$3,混合二甲苯!G:G,"=液1#B",混合二甲苯!F:F,"=装")+SUMIFS(碳8重整!R:R,碳8重整!M:M,">"&$J$3,碳8重整!M:M,"<="&$K$3,碳8重整!G:G,"=液1#B",碳8重整!F:F,"=装")+SUMIFS(航煤!R:R,航煤!M:M,">"&$J$3,航煤!M:M,"<="&$K$3,航煤!G:G,"=液1#B",航煤!F:F,"=装")+SUMIFS(成品柴油!R:R,成品柴油!M:M,">"&$J$3,成品柴油!M:M,"<="&$K$3,成品柴油!G:G,"=液1#B",成品柴油!F:F,"=装")+SUMIFS(PX!R:R,PX!M:M,">"&$J$3,PX!M:M,"<="&$K$3,PX!G:G,"=液1#B",PX!F:F,"=装")+SUMIFS(苯!R:R,苯!M:M,">"&$J$3,苯!M:M,"<="&$K$3,苯!G:G,"=液1#B",苯!F:F,"=装")+SUMIFS(苯乙烯!R:R,苯乙烯!M:M,">"&$J$3,苯乙烯!M:M,"<="&$K$3,苯乙烯!G:G,"=液1#B",苯乙烯!F:F,"=装")+SUMIFS(乙苯!R:R,乙苯!M:M,">"&$J$3,乙苯!M:M,"<="&$K$3,乙苯!G:G,"=液1#B",乙苯!F:F,"=装")+SUMIFS(一乙二醇!R:R,一乙二醇!M:M,">"&$J$3,一乙二醇!M:M,"<="&$K$3,一乙二醇!G:G,"=液1#B",一乙二醇!F:F,"=装")+SUMIFS(二乙二醇!R:R,二乙二醇!M:M,">"&$J$3,二乙二醇!M:M,"<="&$K$3,二乙二醇!G:G,"=液1#B",二乙二醇!F:F,"=装")+SUMIFS(苯酚!R:R,苯酚!M:M,">"&$J$3,苯酚!M:M,"<="&$K$3,苯酚!G:G,"=液1#B",苯酚!F:F,"=装")+SUMIFS(丙酮!R:R,丙酮!M:M,">"&$J$3,丙酮!M:M,"<="&$K$3,丙酮!G:G,"=液1#B",丙酮!F:F,"=装")+SUMIFS(裂解碳九!R:R,裂解碳九!M:M,">"&$J$3,裂解碳九!M:M,"<="&$K$3,裂解碳九!G:G,"=液1#B",裂解碳九!F:F,"=装")+SUMIFS(乙烯焦油!R:R,乙烯焦油!M:M,">"&$J$3,乙烯焦油!M:M,"<="&$K$3,乙烯焦油!G:G,"=液1#B",乙烯焦油!F:F,"=装")+SUMIFS(液硫!R:R,液硫!M:M,">"&$J$3,液硫!M:M,"<="&$K$3,液硫!G:G,"=液1#B",液硫!F:F,"=装")+SUMIFS(甲醇!R:R,甲醇!M:M,">"&$J$3,甲醇!M:M,"<="&$K$3,甲醇!G:G,"=液1#B",甲醇!F:F,"=装")+SUMIFS(硫酸!R:R,硫酸!M:M,">"&$J$3,硫酸!M:M,"<="&$K$3,硫酸!G:G,"=液1#B",硫酸!F:F,"=装")+SUMIFS(液碱!R:R,液碱!M:M,">"&$J$3,液碱!M:M,"<="&$K$3,液碱!G:G,"=液1#B",液碱!F:F,"=装")+SUMIFS(丙烷!R:R,丙烷!M:M,">"&$J$3,丙烷!M:M,"<="&$K$3,丙烷!G:G,"=液1#B",丙烷!F:F,"=装")+SUMIFS(异丁烷!R:R,异丁烷!M:M,">"&$J$3,异丁烷!M:M,"<="&$K$3,异丁烷!G:G,"=液1#B",异丁烷!F:F,"=装")+SUMIFS(丁二烯!R:R,丁二烯!M:M,">"&$J$3,丁二烯!M:M,"<="&$K$3,丁二烯!G:G,"=液1#B",丁二烯!F:F,"=装")+SUMIFS(裂解碳五!R:R,裂解碳五!M:M,">"&$J$3,裂解碳五!M:M,"<="&$K$3,裂解碳五!G:G,"=液1#B",裂解碳五!F:F,"=装")+SUMIFS(丙烯!R:R,丙烯!M:M,">"&$J$3,丙烯!M:M,"<="&$K$3,丙烯!G:G,"=液1#B",丙烯!F:F,"=装")+SUMIFS(乙烯!R:R,乙烯!M:M,">"&$J$3,乙烯!M:M,"<="&$K$3,乙烯!G:G,"=液1#B",乙烯!F:F,"=装")

以上两个公式太长，excel计算缓慢，求大神帮忙精简一下或者给个新的解决方案

jokulboy

咱别的先不说，佩服得五体投地！！！

jokulboy

要不你还是直接说要求吧，这能把人看晕了，天气这么热，容易出问题。。。

cemmm

哇，还没见过这么长的火车。这有一个数据错也得查半天吧。
上传附件，看有没有简单办法，如果确实是条件太多，还是用VBA好。

魚。。

喔唷，太帅了

solextrade

含等号，2373字符，太强大了..................

zxx212

总要有一个附件吧，就这公式，谁知道怎么回事

solextrade

正好有个很相似的帖子参考：

工作需要做的房屋销售台账，想请大家帮忙完善完善
http://club.excelhome.net/thread-1544211-1-1.html
(出处: ExcelHome技术论坛)

2912770391

第一个可以把所有表名定义一个名称，然后用数组公式{=SUM(COUNTIFS(INDIRECT(a&"!M:M"),">"&$J$3,INDIRECT(a&"!M:M"),"<="&$K$3,INDIRECT(a&"!F:F"),"装",INDIRECT(a&"!G:G"),"液1#B"))}第二个简化方式跟第一个一样。

cam333

越长火车，厉害，看了直接晕