SUMPRODUCT具有多个条件的动态列(SUMPRODUCT dynamic columns with multiple criteria)

SUMPRODUCT具有多个条件的动态列(SUMPRODUCT dynamic columns with multiple criteria)

附件是数据的屏幕截图。

我想要做的是总结所有匹配的标准并动态地对列进行求和。

我相信这张照片描述了我想要完成的事情。

为清楚起见，考虑到数据的结构，我想要做的是将相关列（每个期望日期的实际值）以及其他标准相加。

在这个例子中，我想仅为2016年2月至2016年4月的“USA”，“John”和“Milk”的实际数字求和

Attached is the screenshot of the data.

What I am trying to do is to sum all the criterias that match and sum the columns dynamically.

I believe the picture describes what I'd like to accomplish.

For clarity, given the structure of the data, what I'd like to do is to sum the relevant columns (Actuals per desired date) as well as other criterias.

In this example, I'd like to sum only for the Actuals from Feb 2016 to Apr 2016 for "USA", "John" and "Milk"

最满意答案

=SUMPRODUCT(($B$14=$A$3:$A$8)*($B$15=$B$3:$B$8)*($B$16=$C$3:$C$8)*($B$17=$E$2:$P$2)*($B$18<=$E$1:$P$1)*($C$18>=$E$1:$P$1)*($E$3:$P$8))

那应该做的

=SUMPRODUCT(($B$14=$A$3:$A$8)*($B$15=$B$3:$B$8)*($B$16=$C$3:$C$8)*($B$17=$E$2:$P$2)*($B$18<=$E$1:$P$1)*($C$18>=$E$1:$P$1)*($E$3:$P$8))

that should do