具有动态多个条件的 SUMIF/SUMIFS 的 Excel SUM [英] Excel SUM of SUMIF/SUMIFS with dynamic multiple criteria

问题描述

我需要通过单元格引用传递一个多条件列表(一个常量数组)，而不是将它硬输入到我的公式中.
所以，而不是这个:
=SUM(SUMIFS(sum_range,criteria_range,{"red","blue"}))
但我需要使用这个:=SUM(SUMIFS(sum_range,criteria_range,$A1)) 其中 $A1 是 {"red","blue"}

I need to pass a multiple criteria list (a constant array) via cell reference rather than hard-typing it into my formula.
So, instead of this:
=SUM(SUMIFS(sum_range,criteria_range,{"red","blue"}))
But I would need to use this: =SUM(SUMIFS(sum_range,criteria_range,$A1)) where $A1 is {"red","blue"}

我知道可以使用一系列单元格来传递数组，但我确实需要我的条件来自单个单元格.
似乎通过单元格引用传递常量数组只会将第一个元素传递给公式(即仅使用红色"作为条件)以及我能找到的所有工作示例(这里 或 此处) 将条件硬输入到公式中.

I understand that one can use a range of cells to pass an array but I really need my condition to come from a single cell.
It seems that passing a constant array via cell reference only passes the first element to the formula (i.e. only "red" is used as a condition) and all the working examples I could find of this (here or here) are hard-typing the condition into the formula.

任何人都好运吗?

我应该补充一点，我的数据集包含空白行，因此它不是连续的，一般来说，我正在寻找一个不太复杂的解决方案，该解决方案在大多数情况下都可以使用，并且限制和警告尽可能少.

I should add that my data set includes blank rows so it is not contiguous and in general, I'm looking for a not too convoluted solution that will work most of the time and with as little restrictions and caveats as possible.

推荐答案

使用包含 {"red","blue"} 的单元格 A1 然后我设置了一个命名范围 Condition> 我将 =EVALUATE($A1) 分配给它，现在我可以像这样传递我的条件:
=SUM(SUMIFS(W$12:W$448,$I$12:$I$448,$I474,$J$12:$J$448,$J474,$K$12:$K$448,条件))

With cell A1 containing {"red","blue"} I then setup a named range Condition to which I assigned =EVALUATE($A1) and now I can pass my condition like so:
=SUM(SUMIFS(W$12:W$448,$I$12:$I$448,$I474,$J$12:$J$448,$J474,$K$12:$K$448,Condition))

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