在Excel中，将每行中的一列中的所有值相加，其中另一列是特定值

I'm wondering if there is an easy way to do what I'm looking for. Basically, I have a balance sheet in Excel 2011 with a bunch of data. One specific piece of information I always want visible is the amount that hasn't been reimbursed. In other words, I have a column for the amount paid and another for whether or not it has been reimbursed (Yes/No). I want to sum all of the amounts paid where the reimbursed field is equal to 'No'.

我想知道有没有一种简单的方法来做我想要的。基本上，我在Excel 2011中有一份资产负债表，里面有很多数据。我一直想要看到的一个具体信息就是没有报销的金额。换句话说，我有一个列表示支付的金额，另一个列表示是否已经报销(Yes/No)。我想把所有支付给报销字段的金额相加。

I recognize I can sum the entire column and filter out those that have been reimbursed, but I'd like it to display the full amount regardless of what filter is on (or if no filter is on).

我认识到我可以对整个列进行求和，并过滤掉那些已报销的，但是我希望它显示完整的金额，而不管哪个过滤器是on的(或者如果没有过滤器是on的)。

I wasn't able to find good keywords to describe this to Google, so I'm asking here. I would like to accomplish this in Excel, not in an external program or script.

我找不到合适的关键词来描述谷歌，所以我在这里问。我希望用Excel来完成，而不是用外部程序或脚本。

3 个解决方案

#1

If column A contains the amounts to be reimbursed, and column B contains the "yes/no" indicating whether the reimbursement has been made, then either of the following will work, though the first option is recommended:

如果A列包含要报销的金额，B列包含“是/否”，表明是否已报销，则下列任何一项都将有效，但建议采用第一个选项:

=SUMIF(B:B,"No",A:A)

or

或

=SUMIFS(A:A,B:B,"No")

Here is an example that will display the amounts paid and outstanding for a small set of sample data.

下面的示例将显示为一组示例数据支付的和未支付的金额。

 A         B            C                   D
 Amount    Reimbursed?  Total Paid:         =SUMIF(B:B,"Yes",A:A)
 $100      Yes          Total Outstanding:  =SUMIF(B:B,"No",A:A)
 $200      No           
 $300      No
 $400      Yes
 $500      No

#2

You could do this using SUMIF. This allows you to SUM a value in a cell IF a value in another cell meets the specified criteria. Here's an example:

你可以用SUMIF。如果另一个单元格中的值满足指定的条件，那么可以在单元格中对值进行求和。这里有一个例子:

 -   A         B
 1   100       YES
 2   100       YES
 3   100       NO

Using the formula: =SUMIF(B1:B3, "YES", A1:A3), you will get the result of 200.

使用公式:=SUMIF(B1:B3，“YES”，A1:A3)，得到200的结果。

Here's a screenshot of a working example I just did in Excel:

下面是我在Excel中做的一个工作示例的屏幕截图:

#3

You should be able to use the IF function for that. the syntax is =IF(condition, value_if_true, value_if_false). To add an extra column with only the non-reimbursed amounts, you would use something like:

你应该可以用IF函数。语法是=IF(条件，value_if_true, value_if_false)。要添加一个只包含未报销金额的额外列，您可以使用以下内容:

=IF(B1="No", A1, 0)

and sum that. There's probably a way to include it in a single cell below the column as well, but off the top of my head I can't think of anything simple.

总和。也许有一种方法可以将它包含在列下面的一个单元格中，但在我的头顶上，我想不出任何简单的东西。