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
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, "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.
Switch to SUMPRODUCT'
=SUMPRODUCT(C2:C8,--(A2:A8="FEBRUARY"),--(COUNTIFS(A2:A8,A2:A8,B2:B8,B2:B8,D2:D8,"Waiting")=0))
How it works:
C2:C8 - the values to be summed--(A2:A8="FEBRUARY") 1 or 0 for matching month--(COUNTIFS(A2:A8,A2:A8,B2:B8,B2:B8,D2:D8,"Waiting")=0) - 1 or 0 for fully settled codesCOUNTIFS(A2:A8,A2:A8,B2:B8,B2:B8,D2:D8,"Waiting") - counts for each Month and Code, how many are Waiting.COUNTIFS(...) = 0- True or False for count = 0--(...) - turns the True/False into 1/0