答案1
然后你想在 SUMPRODUCT 里面使用 SUMIFS:
首先,我们需要解析查找值,我们可以使用 MID 和 ROW 来创建查找数组:
TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",999)),(ROW($XFD$1:INDEX($XFD:$XFD,LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1))-1)*999+1,999))
这将在 A2 中创建一个查找值数组:{1,2,3,4,5,6}
然后我们将数组传递给 SUMIFS():
SUMIFS(C:C,B:B,TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",999)),(ROW($XFD$1:INDEX($XFD:$XFD,LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1))-1)*999+1,999)))
然后我们使用 SUMPRODUCT 来迭代该数组并对每个结果求和:
=SUMPRODUCT(SUMIFS(C:C,B:B,TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",999)),(ROW($XFD$1:INDEX($XFD:$XFD,LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1))-1)*999+1,999))))