SUMIFS的动态OR条件 [英] Dynamic OR criteria for SUMIFS

问题描述

我有一个类似下面的公式

I have a formula like below

=SUM(SUMIFS('Sheet1'!$AK:$AK,'Sheet1'!$AL:$AL,"<=0",'Sheet1'!$N:$N,C2))

我希望C2是动态的多个条件OR字段，范围可能是1到4个条件.

I want the C2 to be a dynamic multiple criteria OR field which might range from 1 to 4 criteria.

如果它是静态的，则公式将如下所示

If it would have been static the formula would be something like below

 =SUM(SUMIFS('Sheet1'!$AK:$AK,'Sheet1'!$AL:$AL,"<=0",'Sheet1'!$N:$N,{"262","261","200"}))

我该怎么做?我无法将其与{"262"，"261"，"200"}作为C2中的值一起使用.

How do I do it ? I can't get it to work with {"262","261","200"} as value in C2.

在C2，C3，C4中使用不同的值后，下面的方法也不起作用

The below doesn't work either after having different values in C2,C3,C4

=SUM(SUMIFS('Sheet1'!$AK:$AK,'Sheet1'!$AL:$AL,"<=0",'Sheet1'!$N:$N,{C2,C3,C4}))

推荐答案

尝试以下操作:

=SUM(SUMIFS(Sheet1!$AK:$AK,Sheet1!$AL:$AL,"<=0",Sheet1!$N:$N,FILTERXML("<t><s>" & SUBSTITUTE(C2, ",", "</s><s>") & "</s></t>", "//s")))

贷给VafāSarmast ，用于拆分为数组技术.

Credit to Vafā Sarmast for splitting to array technique.

似乎通过使用评估公式，数字最终被封装在<s>标记中，然后通过//s的xpath使用这些标记，以将所有匹配项作为列表返回，即以数组形式返回数字.要插入标签，请在现有定界符上使用替代符以及串联(& "</s></t>").至少，这是我的理解.

It seems, from using evaluate formula, that the numbers end up being enclosed in <s> tags, which are then used via xpath of //s, to return all matching items as a list i.e. the numbers as an array. To insert the tags substitute is used on the existing delimiter along with concatenation (& "</s></t>"). At least, that is my understanding.

输入 Ctrl + Shift + 输入作为数组公式.

Enter with Ctrl + Shift + Enter as array formula.

值以C2中的逗号分隔

信息:

FILTERXML

这篇关于SUMIFS的动态OR条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！