☰

乘积相加并除以

tag-icon • tag-icon microsoft-excel worksheet-function
乘积相加并除以

请参阅下面的 Sumproduct 公式。nb. 1 = 6890 & nb. 2 = 8201 的结果。我想将 nb. 1 除以 2，结果 % 应为 84%。但是，如果我合并公式（请参阅 nb. 3），结果将为 100% 而不是 84%。我如何将两个 sumproduct 公式相加，然后将其除以另一个相加的 sumproduct？

  1. =SUMPRODUCT((Doli_Dt>=$C$9)*(Doli_Dt<=$C$10)*(Doli_Act="Phone")*(Doli_Stat="Answered < 20s")*(Doli_db))+SUMPRODUCT((SF_CALLS_WEEK=$B$8)*(SF_CALLS_STATUS="ANSWERED")*(SF_CALLS_ANSWERED_20sec="OK"))

  2. =SUMPRODUCT((Doli_Dt>=$C$9)*(Doli_Dt<=$C$10)*(Doli_Act="Phone")*(Doli_Stat="answered")*(Doli_db))+SUMPRODUCT((SF_CALLS_WEEK=$B$8)*(SF_CALLS_STATUS="ANSWERED"))

  3. =IFERROR(ROUND(SUMPRODUCT(((Doli_Dt>=$C$9)*(Doli_Dt<=$C$10)*(Doli_Act="Phone")*(Doli_Stat="Answered < 20s")*(Doli_db))+SOMMEPROD((SF_CALLS_WEEK=$B$8)*(SF_CALLS_STATUS="ANSWERED")*(SF_CALLS_ANSWERED_20sec="OK")))/SUMPRODUCT(((Doli_Dt>=$C$9)*(Doli_Dt<=$C$10)*(Doli_Act="Phone")*(Doli_Stat="answered")*(Doli_db))+SUMPRODUCT((SF_CALLS_WEEK=$B$8)*(SF_CALLS_STATUS="ANSWERED"))),2),"-")

答案1

@Rajesh S：已经找到解决方案，见下面的公式。通过在正确的位置添加和删除一些括号（特别是在 SUMPRODUCT 之前）解决了这个问题。

=IFERROR(ROUND((SUMPRODUCT((Doli_Dt>=$C$7)*(Doli_Dt<=$C$8)*(Doli_Act="Phone")*(Doli_Stat="Answered < 20s")*(Doli_db))+(SUMPRODUCT((SF_CALLS_STATUS="ANSWERED")*(SF_CALLS_ANSWERED_20sec="OK")*(SF_CALLS_WEEK=$B$6))))/(SUMPRODUCT((Doli_Dt>=$C$7)*(Doli_Dt<=$C$8)*(Doli_Act="Phone")*(Doli_Stat="answered")*(Doli_db))+(SUMPRODUCT((SF_CALLS_STATUS="ANSWERED")*(SF_CALLS_WEEK=$B$6)))),2),"")

相关内容