在我直接回答你的问题之前，需要注意的是 COUNTIFS() 在新的 Google Spreadsheets 中可用，并且这个公式很多更容易.

Before I answer you question directly, it should be noted that COUNTIFS() is available in new Google Spreadsheets and this formula is much easier.

但是，您特别询问了如何使用 ARRAYFORMULA() 做到这一点.

However, you specifically asked how would one do this using ARRAYFORMULA().

首先，让我们分解一个ARRAYFORMULA():

First, let's decompose an ARRAYFORMULA():

我将使用在名为:Archive

假设我在 Master 表上执行以下操作:=Arrayformula(Int(Archive!B$1:B$10))

Let's say I do the following on the Master sheet: =Arrayformula(Int(Archive!B$1:B$10))

我最终得到以下输出.注意我没有拖下来.Google 电子表格会自动执行此操作，因为该公式会返回一整列，而不是逐个单元格执行函数

I end up with the following being output. Note I DID NOT drag down. Google spreadsheets does this automatically, because the formula returns an entire column rather than performing a cell by cell function

现在您看到了 arrayformula() 概念，我们可以构建您的公式.

Now that you see the arrayformula() concept, we can build up your formula.

首先，对于您的标准，您有:它必须在昨天完成(标准 1)，并且它必须等于某个项目(标准 2).

First, for your criteria you have: It must be completed yesterday (criteria 1), and It must equal a certain project (criteria 2).

使用它，我们将编写一个公式来找到这些的交集.一个合乎逻辑的想法是在 if 语句中使用AND()"，但这不起作用，因为它会尝试找到整个集合的逻辑交集(逻辑与).

Using that we will compose a formula that will find the intersection of these. A logical thought would be to use 'AND()' in an if statement, but that won't work because it will attempt to find the logical intersection (logical AND) of the entire set.

因此，我们将利用任何数字乘以 0 为 0"的结果，并使我们想要的所有条件都为一个 arrayformula 中的 1，并且所有条件为另一个标准是单独的数组公式中的 1，如下所示:标准 1:=arrayformula(if(int(Archive!B$1:B)=Today()-1,1,0))标准 2:=arrayformula(if(Archive!A$1:A=A2,1,0))(注意:在这个公式中，我在 A 列中做了一个查找表来比较，即A2".)

So we are going to take advantage of the "any number times 0 is 0"-result and make all things that we want for one criteria be a 1 in one arrayformula and all the things for the other criteria be a 1 in a separate arrayformula, like so: criteria 1: =arrayformula(if(int(Archive!B$1:B)=Today()-1,1,0)) criteria 2: =arrayformula(if(Archive!A$1:A=A2,1,0)) (Note: In this formula, I made a look up table in the A column to compare against, ie "A2".)

然后我们将使用 sumproduct() 将它们组合起来.
=sumproduct(arrayformula(if(int(Archive!B$1:B)=Today()-1,1,0)),arrayformula(if(Archive!A$1:A=A2,1,0)))

Then we will use sumproduct() to combine them.
=sumproduct(arrayformula(if(int(Archive!B$1:B)=Today()-1,1,0)),arrayformula(if(Archive!A$1:A=A2,1,0)))

以下是主屏幕上的输出.

The following is the output on the master screen.