I've tried searching for posts specifically on this, but am struggling to find what I'm looking for. I'm trying to do some fairly basic calculations based on a value in a drop down field for each row which determines whether the value is negative or positive.
我试图专门搜索这个帖子,但我很难找到我正在寻找的东西。我正在尝试根据每行的下拉字段中的值进行一些相当基本的计算,以确定该值是负还是正。
In the example below, I would like to sum all the values in Column B based on a drop down value (Column C) which defines whether the value in column be should be added or subtracted from the total amount in C10.
在下面的示例中,我想根据下拉值(C列)对B列中的所有值进行求和,该值定义是否应在C10中的总量中添加或减去列中的值。
I'll be honest, I have no idea how to even begin working out how to write the formula for the Credit/Debit values based on the drop down.
老实说,我不知道如何根据下拉菜单开始研究如何编写Credit / Debit值的公式。
A B C D
1 DATE VALUE TYPE DESCRIPTION
---- ----- ---- -----------
2 02/03/2015 £150 Debit Currency Exchange
3 20/04/2015 £200 Debit Currency Exchange
4 04/05/2015 £1000 Credit Currency Exchange
5
6
7
8
9
10 TOTAL ADJUSTMENTS: £650
I'm guessing I need to somehow use the SUMIF statement to define whether the type in Column C is Debit or Credit and then add/substract the corresponding value some how from the total amount?
我猜我需要以某种方式使用SUMIF语句来定义C列中的类型是Debit还是Credit,然后根据总量添加/减去相应的值?
Many thanks in advance for any assistance you may be able to offer.
非常感谢您提供的任何帮助。
1 个解决方案
#1
Try
=SUMIF(C2:C9,"Credit",B2:B9)-SUMIF(C2:C9,"Debit",B2:B9)
The first Sumif sums up all the credits, the second Sumif sums up all the debits and the debit sum is then subtracted from the credit sum.
第一个Sumif汇总所有信用,第二个Sumif汇总所有借方,然后从贷方金额中扣除借方金额。
#1
Try
=SUMIF(C2:C9,"Credit",B2:B9)-SUMIF(C2:C9,"Debit",B2:B9)
The first Sumif sums up all the credits, the second Sumif sums up all the debits and the debit sum is then subtracted from the credit sum.
第一个Sumif汇总所有信用,第二个Sumif汇总所有借方,然后从贷方金额中扣除借方金额。