如果唯一值不在Excel中处于相同状态，则排除sumifs中的加法 [英] Exclude addition in sumifs if unique value is not in the same status in Excel

问题描述

我试图在下面获取以下数据的总和，但是我在制定逻辑时陷入困境.基本上，我要获取的是每月已结算的所有金额 的总和.我已经使用 SUMIFS 做到了这一点.但是，我希望能够考虑到代码"列.请在下面查看示例数据.

I am trying to get the sum of the following data below but I'm stuck in formulating the logic. Basically, what I am trying to get is the sum of all settled amount per month. I have accomplished that using SUMIFS. However, I want to be able to take into account the Code column. Please see sample data below.

MONTH   |CODE  |CONTRIBUTION|STATUS
JANUARY |CS-111|500         |Settled
JANUARY |CS-121|500         |Settled
FEBRUARY|CS-131|200         |Settled    
FEBRUARY|CS-131|200         |Waiting
FEBRUARY|CS-141|300         |Settled
MARCH   |CS-151|400         |Waiting
MARCH   |CS-161|700         |Settled

请查看2月份的数据.我们有两个代码，CS-131和CS-141.对于CS-131，只有第一个条目已建立，而第二个条目正在等待.对于CS-141的唯一条目，其状态已确定.鉴于此，我希望公式不考虑CS-131的金额，因为两个条目均未结算，但由于已结算，因此继续添加CS-141的金额.

Please see the data for February. We have two codes, CS-131 and CS-141. For CS-131, only the first entry is settled while the second one is waiting. For CS-141's only entry, the status is settled. Given this, I want the formula to disregard counting the amount for CS-131 since both entries are not settled but continue to add the amount for CS-141 since it is settled.

到目前为止，这是我的公式.

This is my formula so far.

= SUMIFS($ C $ 2:$ C $ 8，$ A $ 2:$ A $ 8，"February"，$ D $ 2:$ D $ 8，已解决")

=SUMIFS($C$2:$C$8, $A$2:$A$8,"February", $D$2:$D$8, "Settled")

有什么想法可以将什么样的功能集成到其中?

Any ideas what kind of function I can incorporate to this?

如果已经问过这个问题，很多道歉.预先谢谢你.

Many apologies if this question has been asked before. Thank you in advance.

推荐答案

切换到SUMPRODUCT'

=SUMPRODUCT(C2:C8,--(A2:A8="FEBRUARY"),--(COUNTIFS(A2:A8,A2:A8,B2:B8,B2:B8,D2:D8,"Waiting")=0))

工作原理:

1. C2:C8-要求和的值
2. --(A2:A8="FEBRUARY") 1或0匹配月份
3. --(COUNTIFS(A2:A8,A2:A8,B2:B8,B2:B8,D2:D8,"Waiting")=0)-1或0用于完全结算的代码
   3.1. COUNTIFS(A2:A8,A2:A8,B2:B8,B2:B8,D2:D8,"Waiting")-计算每个月和代码，等待的次数.
   3.2 COUNTIFS(...) = 0-True或False的计数= 0
   3.3 --(...)-将True/False转换为1/0

1. C2:C8 - the values to be summed
2. --(A2:A8="FEBRUARY") 1 or 0 for matching month
3. --(COUNTIFS(A2:A8,A2:A8,B2:B8,B2:B8,D2:D8,"Waiting")=0) - 1 or 0 for fully settled codes
   3.1. COUNTIFS(A2:A8,A2:A8,B2:B8,B2:B8,D2:D8,"Waiting") - counts for each Month and Code, how many are Waiting.
   3.2 COUNTIFS(...) = 0- True or False for count = 0
   3.3 --(...) - turns the True/False into 1/0

这篇关于如果唯一值不在Excel中处于相同状态，则排除sumifs中的加法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！