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

问题描述

我想知道是否有一个简单的方法来做我正在寻找的东西。基本上，我在Excel 2011中有一堆资料表。我一直想要看到的一个特定的信息是没有报销的数量。换句话说，我有一个支付金额的列，另一个是否已经报销（是/否）。我想把所有付款的金额都归结于所报酬的字段等于否。

我认识到我可以总结整个列，并将那些已经被报销，但我希望它显示全部的金额，无论过滤器是什么（或如果没有过滤器）。

我无法找到很好的关键字来形容这个Google，所以我在这里问。我想在Excel中完成，而不是外部程序或脚本。

解决方案

如果列A包含的数量报销，列B包含是/否，表示是否已报销，那么以下任何一项都可以正常运行，但建议使用第一个选项：

  = SUMIF（B：B，否，A：A）
  

或

  = SUMIFS（A：A，B：B，否）
  

这是一个示例，显示一小部分样本数据支付和未偿还的金额。

  ABCD 
报销金额？支付总额：= SUMIF（B：B，是，A：A）
 $ 100是总计未完成：= SUMIF（B：B，否，A：A）
 $ 200否
 $ 300否
 $ 400是
 $ 500否
  

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

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.

解决方案

If column A contains the amounts to be reimbursed, and column B contains the "yes/no" indicating of 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)

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

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