本帖最后由 mjm04 于 2024-4-23 15:05 编辑
=SUMPRODUCT((A:A=K2)*(INDIRECT(CHOOSE(RIGHT($L$1,1),"B:B","C:C","D:D","E:E","F:F","G:G","H:H","I:I"))="已完成"))
原函数:=SUMPRODUCT((A:A=K2)*(H:H="已完成"))
把标红的部份换成:INDIRECT(CHOOSE(RIGHT($L$1,1),"B:B","C:C","D:D","E:E","F:F","G:G","H:H","I:I")就行了;
你在五楼说的那个问题,是SUMPRODUCT参数设置的问题,你只设定了一个判断条件;
你可以这样写
=SUMPRODUCT((A:A=K2)*(INDIRECT(CHOOSE(RIGHT($L$1,1),"B:B","C:C","D:D","E:E","F:F","G:G","H:H","I:I"))={"已完成","关键字-2","关键字-3","关键字-N"}))
数组函数,高版本直接回车,低版本数组三键;
{ }里面的内容,是“或”的关系,用逗号连接 表示只要满足里面的条件都要计数;
|