在 Excel 中，对每一行中的一列中的所有值求和，其中另一列是特定值 [英] In Excel, sum all values in one column in each row where another column is a specific value

问题描述

我想知道是否有一种简单的方法可以完成我正在寻找的事情.基本上，我在 Excel 2011 中有一个带有一堆数据的资产负债表.我一直希望看到的一项特定信息是尚未报销的金额.换句话说，我有一栏是支付金额，另一栏是是否已经报销(是/否).我想对已报销字段等于否"的所有已支付金额求和.

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'.

我知道我可以对整个列求和并过滤掉已报销的那些，但我希望它显示全部金额，而不管使用什么过滤器(或者如果没有使用过滤器).

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).

我找不到合适的关键字来向 Google 描述这一点，所以我在这里询问.我想在 Excel 中完成此操作，而不是在外部程序或脚本中.

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.

推荐答案

如果 A 列包含要报销的金额，而 B 列包含是/否"表示是否已报销，则以下任一会起作用，但建议使用第一个选项:

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:

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

或

=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

这篇关于在 Excel 中，对每一行中的一列中的所有值求和，其中另一列是特定值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！