1

I am trying to get the total count of entries per month based on the status ("Settled") and the distinct code. If the other status of the other entries in that certain code is not yet settled, it shouldn't count. Please see example below and the expected output.

MONTH   |CODE  |CONTRIBUTION|STATUS
JANUARY |CS-111|500         |Settled
JANUARY |CS-111|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

Sample output:

January  | 1  | 1 because same code and status
February | 1  | 1 because only CS-141 is settled, the second entry of CS-131 is Waiting
March    | 1  | 1 because only CS-161 is settled

This is my formula based on this question:

=SUMPRODUCT(--($A$2:$A$8="January"),--(COUNTIFS($A$2:$A$8,$A$2:$A$8,$B$2:$B$8,$B$2:$B$8,$D$2:$D$8,"Settled")=0))

With the formula above, I get 2 as the output for January and not 1. 1 because they have the same code and status. Could you kindly help me which concept I should apply to get the expected output? Hope my question is clear. Thank you very much.

Apologies if this question has been asked before.

JvdV
  • 70,606
  • 8
  • 39
  • 70
Isabella
  • 455
  • 1
  • 10
  • 23

1 Answers1

2

Imagine this setup:

enter image description here

Formula in G2:

=SUM(--(FREQUENCY(IF(($A$2:$A$8=G2)*($D$2:$D$8="Settled"),IF(COUNTIFS($B$2:$B$8,$B$2:$B$8,$D$2:$D$8,"Waiting")=0,MATCH($B$2:$B$8,$B$2:$B$8,0))),ROW($B$2:$B$8)-ROW($B$2)+1)>0))

Note: It's an array formula and needs to be confirmed through Ctrl+Shift+Enter

Drag down...

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • Wow!! Thank you for this. I'm looking up the frequency concept to help me better understand it. However, I tried the formula on my end and when I changed the status of the first entry for January for CS-111 to Waiting, the value is still 1. It should return the value of 0. Any thoughts? – Isabella Nov 11 '19 at 17:41
  • Yes because the second one is still on settled @Isabella =) – JvdV Nov 11 '19 at 18:25
  • Is there any way we could get the output of 0 if the status of the first entry in January with code CS-111 is "Waiting"? Because technically not all entries for CS-111 is settled? Thank you very much! – Isabella Nov 11 '19 at 18:30
  • @Isabella, I've updated the formula to account for that scenario :). Please, if this has answered your question, consider to upvote/accept the answer to return the favor =) – JvdV Nov 12 '19 at 07:39
  • Hi, @JvdV. Apologies for the late reply, I was on travel. Wow, this worked like magic. Thank you very very much!!! – Isabella Nov 20 '19 at 04:30