SUMPRODUCT于DAY函数搭配错误求助

510437958

由于系统导出时间格式为年月日加时分秒，所以如果要求计算指定日期时间段的销售金额（多条件）采用了辅助列格式，结果计算错误。

1、O3单元格=IF($W3<>$X3,SUMPRODUCT(Sheet2!$J$2:$J$30000*(Sheet2!$D$2:$D$30000=O$2)*(Sheet2!$R$2:$R$30000<>"已开票未缴款")*(Sheet2!$R$2:$R$30000<>"货款对应发票已作废")*(Sheet2!$Y$2:$Y$30000<=$X3))-SUMPRODUCT(Sheet2!$J$2:$J$30000*(Sheet2!$D$2:$D$30000=O$2)*(Sheet2!$Y$2:$Y$30000<=($W3-1))*(Sheet2!$R$2:$R$30000<>"已开票未缴款")*(Sheet2!$R$2:$R$30000<>"货款对应发票已作废")),SUMPRODUCT(Sheet2!$J$2:$J$30000*(Sheet2!$D$2:$D$30000=O$2)*(Sheet2!$Y$2:$Y$30000=$X3)*(Sheet2!$R$2:$R$30000<>"已开票未缴款")*(Sheet2!$R$2:$R$30000<>"货款对应发票已作废")))但是取数结果错误
2、2、经过测试是Sheet2的DAY函数取值计算与按天进度的SUMPRODUCT搭配结果有误，不知道是语法还是格式问题，从昨晚一直到现在也没有解决。O3单元格本意为计算货款状态不为货款对应发票已作废、已开票未缴款的11产品从5日到11日的销售金额，正确结果应为2777591.70元，但计算结果却为9666.80元。
详细情况见附件，要求1、用函数解决，越简洁易懂越好；2、可以在2007实现

袁勇

Q3:

1. =MMULT(SUMIFS(Sheet2!$J:$J,Sheet2!$D:$D,O$2,Sheet2!$Y:$Y,">="&$W3,Sheet2!$Y:$Y,"<="&$X3,Sheet2!R:R,{"<>","已开票未缴款","货款对应发票已作废"}),{1;-1;-1})

复制代码

袁勇

回复在审核中，结果为2776886.9。与你要求的结果2777591.70元不一样。因为20日有一笔已已开票未缴款-704.8不符合开始截止日期范围。

zhaoxx

你这最终结果没有排除已开票未缴款呀 =SUMIFS(Sheet2!J:J,Sheet2!D:D,按天进度!O2,Sheet2!X:X,">="&5,Sheet2!X:X,"<="&15,Sheet2!Q:Q,"<>"&"已开票未缴款",Sheet2!Q:Q,"<>"&"货款对应发票已作废")
下面这个跟你说的结果一样。
=SUMIFS(Sheet2!J:J,Sheet2!D:D,按天进度!O2,Sheet2!X:X,">="&5,Sheet2!X:X,"<="&15)

theking_wsz

w x 列取数据后是文本格式  好像比较时不是按数值大小      改为=IF(ISNUMBER(FIND("-",B3)),RIGHT(B3,LEN(B3)-FIND("-",B3)),B3)*1
   (Sheet2!$Y$2:$Y$30000<=($W3-1))   这一截  该是这样吧   (Sheet2!$Y$2:$Y$30000>=$W3)    但最后的结果和你说的还是不一样

zhaoxx

sumifs应该可以吧。怎么回复了也不显示呀

大跑丁

=IF($W3<>$X3,SUMPRODUCT(Sheet2!$J$2:$J$1386*(Sheet2!$D$2:$D$1386=O$2)*(Sheet2!$R$2:$R$1386<>"已开票未缴款")*(Sheet2!$R$2:$R$1386<>"货款对应发票已作废")*(Sheet2!$Y$2:$Y$1386<=$X3)*(Sheet2!$Y$2:$Y$1386>($W3-1))),SUMPRODUCT(Sheet2!$J$2:$J$1386*(Sheet2!$D$2:$D$1386=O$2)*(Sheet2!$Y$2:$Y$1386=$X3)*(Sheet2!$R$2:$R$1386<>"已开票未缴款")*(Sheet2!$R$2:$R$1386<>"货款对应发票已作废")))

把W和X列转换成数字，加个 --
=--IF(ISNUMBER(FIND("-",B3)),LEFT(B3,FIND("-",B3)-1),B3)
=--IF(ISNUMBER(FIND("-",B3)),RIGHT(B3,LEN(B3)-FIND("-",B3)),B3)

我走我流

不太 明白意思，，，
=SUMPRODUCT((ISNUMBER(MATCH(--TEXT(LEFT(Sheet2!$O$2:$O$30000,10)&"","d;;;!0"),IFERROR(ROW(INDIRECT(SUBSTITUTE(B3,"-",":"))),B2:B2),)))*(Sheet2!$D$2:$D$30000=Sheet2!D2)*ISNA(MATCH(Sheet2!$R$2:$R$30000,{"已开票未缴款","货款对应发票已作废"},)),Sheet2!$J$2:$J$30000)

只做了2个处理，一个是日期，用MATCH 函数
2个后面的开面的未缴款 和已作废也用MTACH 处理，如果是去除这2个条件用ISNA
如果是仅仅算这2个的话，把ISNA 换成ISNUMBER ,仅供参考

CaoZ

我算是 2776886.90

1. IF($W3<>$X3,SUMIFS(Sheet2!$J:$J,Sheet2!$D:$D,O$2,Sheet2!$R:$R,"<>已开票未缴款",Sheet2!$R:$R,"<>货款对应发票已作废",Sheet2!$Y:$Y,">="&$W3,Sheet2!$Y:$Y,"<="&$X3),SUMIFS(Sheet2!$J:$J,Sheet2!$D:$D,O$2,Sheet2!$R:$R,"<>已开票未缴款",Sheet2!$R:$R,"<>货款对应发票已作废",Sheet2!$Y:$Y,"="&$X3))

复制代码

CaoZ

1. =IF($W3<>$X3,SUMIFS(Sheet2!$J:$J,Sheet2!$D:$D,O$2,Sheet2!$R:$R,"<>已开票未缴款",Sheet2!$R:$R,"<>货款对应发票已作废",Sheet2!$Y:$Y,">="&$W3,Sheet2!$Y:$Y,"<="&$X3),SUMIFS(Sheet2!$J:$J,Sheet2!$D:$D,O$2,Sheet2!$R:$R,"<>已开票未缴款",Sheet2!$R:$R,"<>货款对应发票已作废",Sheet2!$Y:$Y,"="&$X3))

复制代码