Excel求一个月内不同时间段不同组合的销售业绩
发布网友
发布时间：2022-08-24 19:17
共3个回答
热心网友
时间：2024-09-08 19:54
=SUM(SUMIFS($C:$C,$A:$A,">="&$G$11,$A:$A,"<="&$G$11+9,$B:$B,IF(ISNUMBER(FIND({"A","B","C","X","Y","Z"},G5)),{"A","B","C","X","Y","Z"},"")))
=SUM(SUMIFS($C:$C,$A:$A,">="&$G$11+10,$A:$A,"<="&$G$11+19,$B:$B,IF(ISNUMBER(FIND({"A","B","C","X","Y","Z"},G6)),{"A","B","C","X","Y","Z"},"")))
=SUM(SUMIFS($C:$C,$A:$A,">="&$G$11+20,$A:$A,"<="&$H$11,$B:$B,IF(ISNUMBER(FIND({"A","B","C","X","Y","Z"},G7)),{"A","B","C","X","Y","Z"},"")))
选择G13:G15，右拉填充
只是统计一下次数（成交的笔数），可用如下公式
=SUM(COUNTIFS($A:$A,">="&$G$11,$A:$A,"<="&$G$11+9,$B:$B,IF(ISNUMBER(FIND({"A","B","C","X","Y","Z"},G5)),{"A","B","C","X","Y","Z"},"")))
=SUM(COUNTIFS($A:$A,">="&$G$11+10,$A:$A,"<="&$G$11+19,$B:$B,IF(ISNUMBER(FIND({"A","B","C","X","Y","Z"},G6)),{"A","B","C","X","Y","Z"},"")))
=SUM(COUNTIFS($A:$A,">="&$G$11+20,$A:$A,"<="&$H$11,$B:$B,IF(ISNUMBER(FIND({"A","B","C","X","Y","Z"},G7)),{"A","B","C","X","Y","Z"},"")))追问你好大师，我想再问问，上面我们计算的是合计的金额，假如我们只是统计一下次数（成交的笔数）该如何修改公式？（上旬：A组是成交3笔、B和C组各成交1笔）
热心网友
时间：2024-09-08 19:54
热心网友
时间：2024-09-08 19:55
=SUMPRODUCT((MATCH(DAY($A$2:$A$20),{1,11,21})=ROW(A1))*IFERROR(FIND($B$2:$B$20,INDEX({"A","B","C"}&{"X";"Y";"Z"},MOD(ROW(A1)+COLUMN(A1)+1,3)+1,COLUMN((A1)))),)*$C$2:$C$20)
