多区域的数组函数如何下拉填充

栀

函数=SUM(COUNTIF(INDIRECT({"AH5:AI5","AL5:AP5","AS5:AW5","AZ5:BB5","BF5:BK5"}),"常驻地"))

下拉填充还是这样=SUM(COUNTIF(INDIRECT({"AH5:AI5","AL5:AP5","AS5:AW5","AZ5:BB5","BF5:BK5"}),"常驻地"))
我希望下拉是=SUM(COUNTIF(INDIRECT({"AH6:AI6","AL6:AP6","AS6:AW6","AZ6:BB6","BF6:BK6"}),"常驻地"))
请问如何修改函数，谢谢！

良良

试试：=SUM(COUNTIF(OFFSET(INDIRECT({"AH5:AI5","AL5:AP5","AS5:AW5","AZ5:BB5","BF5:BK5"}),ROW(A1),),"常驻地"))

栀

良良 发表于 2023-6-20 19:03
试试：=SUM(COUNTIF(OFFSET(INDIRECT({"AH5:AI5","AL5:AP5","AS5:AW5","AZ5:BB5","BF5:BK5"}),ROW(A1),)," ...

好像不成，之前得出的数字是3，这条公式得出是9。我上传一下附件，您看看

高个子

你这个在第二行标记通勤日和非通勤日会更好一些

栀

不好意思，我没太理解。可以直接写出来或改在附件吗

良良

栀 发表于 2023-6-21 09:03
好像不成，之前得出的数字是3，这条公式得出是9。我上传一下附件，您看看

CS4=SUM(COUNTIF(OFFSET(INDIRECT({"$AH$3:$AI$3","$AL$3:$AP$3","$AS$3:$AW$3","$AZ$3:$BB$3","$BF$3:$BK$3"}),ROW(A1),),"常驻地"))

栀

良良 发表于 2023-6-21 10:05
CS4=SUM(COUNTIF(OFFSET(INDIRECT({"$AH$3:$AI$3","$AL$3:$AP$3","$AS$3:$AW$3","$AZ$3:$BB$3","$BF$3:$B ...

可以了，谢谢谢谢！

hcm19522

U2=SUMPRODUCT(COUNTIF(OFFSET(INDIRECT(SUBSTITUTE(W$2:W$5,":","1:")&1),ROW(A1),),W$8))