0

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.

Isabella
  • 455
  • 1
  • 10
  • 23
  • Your question is not clear for me. As per your formula, that one will sum whatever value on column C if `month=February` and `Status=Settled`. That one is your settled statement for the first CS-131, so the formula is working. But you are asking for an expected output 0, when you mention that both CS-131 are not settled? That one is not according to your screenshot. Try to describe better what do you need, not the expected value better what do you need as expected value. – David García Bodego Oct 23 '19 at 06:39
  • Hi, @David. Thank you for your response. For February, we have two entries for CS-131. One entry is settled, the other one is waiting. If not all entries are Settled for CS-131 then it shouldn't add the amount in Column C. Thank you! – Isabella Oct 23 '19 at 07:00

2 Answers2

1

If I understand your data and question correct then you can just do a subtract of the sumifs with "waiting".

=SUMIFS($C$2:$C$8, B2:B8,"CS-131", $A$2:$A$8,"February", $D$2:$D$8, "Settled")-SUMIFS($C$2:$C$8, B2:B8,"CS-131", $A$2:$A$8,"February", $D$2:$D$8, "Waiting")

If there is a waiting then that sum is subtracted from the settled sum and results in 0.

If you want to make negative numbers 0 because there is no settled but only waiting then you can just wrap the sumifs()-sumifs() with a max().

=MAX(0,SUMIFS($C$2:$C$8, B2:B8,"CS-131", $A$2:$A$8,"February", $D$2:$D$8, "Settled")-SUMIFS($C$2:$C$8, B2:B8, "CS-131", $A$2:$A$8,"February", $D$2:$D$8, "Waiting"))

If the result is negative then 0 is displayed, else the sum.

Edit: added the condition that it should only sum "CS-131". It's hardcoded in the formula but that can easily be a cell reference.

enter image description here

Andreas
  • 23,610
  • 6
  • 30
  • 62
  • Hi. Thank you for your response. I tried the logic you presented but it didn't work. Perhaps my question was confusing. For February, we have two entries for CS-131. One entry is settled, the other one is waiting. If not all entries are Settled then it shouldn't add the amount in Column C. Thank you! – Isabella Oct 23 '19 at 06:57
  • And this sums them but also sums the waiting and subtracts the two. If that does not work then something is not as the image shows. update your question with the table instead so that we can use your data without manually retyping everything. – Andreas Oct 23 '19 at 06:59
  • Hi. I have updated the table and description. Basically we have two distinct codes for February: CS-131 (2 entries) and CS-141 (1 entry). Since not all entries for CS-131 is "Settled" then their amounts in column C should be disregarded. Now with CS-141, since the status is "Settled", the output for sumif should be 300. Thank you! – Isabella Oct 23 '19 at 07:12
  • ***Now with CS-141, since the status is "Settled", the output for sumif should be 300.*** That was unclear since the formula did not even include this part. I still can't see a table that I can copy paste. – Andreas Oct 23 '19 at 07:38
  • Hi. I have finally updated the table from image to text. Kindly check. – Isabella Oct 23 '19 at 07:56
  • @Isabella Updated answer! – Andreas Oct 23 '19 at 09:17
1

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:

  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
chris neilsen
  • 52,446
  • 10
  • 84
  • 123
  • Hi. It's me again. I was wondering how I could use this formula in the context of count. Instead of getting the sum, I would like to get the count. Same concept applies but instead of the output as the total sum, I would get the count. Thank you very much! – Isabella Nov 11 '19 at 03:36
  • Just remove the reference to the Contribution column, so it's `=SUMPRODUCT(--(A2:A8="FEBRUARY"),--(COUNTIFS(A2:A8,A2:A8,B2:B8,B2:B8,D2:D8,"Waiting")=0))` – chris neilsen Nov 11 '19 at 03:59
  • Hi. Good day. I have posted a new question. COuld you kindly check? https://stackoverflow.com/questions/58797044/count-duplicate-cell-values-as-one-if-multiple-criteria-are-met – Isabella Nov 11 '19 at 07:20