Excel Q-具有二维数组的SUMIFS [英] Excel Q - SUMIFS with a 2 dimensional array

问题描述

我有一个二维数组:水平轴上的日期和垂直轴上的标识号.

I have a 2-D array: dates on a horizontal axis and identification numbers on a vertical axis.

我想要以特定日期和ID为条件的总和，并且我想知道如何使用SUMIFS进行此操作.

I want the sums conditioned on a particular date and ID, and I want to know how to do this using SUMIFS.

由于某种原因，由于数组是2D而条件范围是1D，因此我似乎无法.谁能给我关于我可以使用的其他公式的任何建议?

For some reason, it seems like I cannot since the array is 2-D while the criteria ranges are 1-D. Can anyone give me any advice on other formulas I can use?

换句话说，我想添加满足我选择的ID和日期的值；有一个或多个满足条件的数据点.这就是为什么SUMIF函数很重要的原因.

In other words, I would like to add the values that satisfy the ID and date I select; there is one or more data point that satisfies the conditions. This is why the SUMIF function is relevant.

推荐答案

使用此数据，您将无法使用SUMIF论坛.这是您可以使用的公式:

With this data you will not be able to use a SUMIF forumula. Here's a formula you can use:

=SUM(IF($B$2:$B$6=C9,IF($F$1:$K$1=B9,$F$2:$K$6)))

在适当的地方更改地址，并确保按CTRL + SHIFT + ENTER输入.您还可以使用以下公式来避免按CTRL + SHIFT + ENTER:

Change the addresses where appropriate and be sure and enter it by pressing CTRL + SHIFT + ENTER. You can also use the below formula to avoid pressing CTRL + SHIFT + ENTER:

=SUMPRODUCT(($B$2:$B$6=C9)*($F$1:$K$1=B9)*$F$2:$K$6)

这篇关于Excel Q-具有二维数组的SUMIFS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！