Count unique values with condition

[rlobera]

Hi everyone, hope you can help me with this issue.

I have a sheet that looks like this

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Subscriber_ID[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]09/01/18[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]202[/TD]
[TD]08/13/18[/TD]
[/TR]
[TR]
[TD]203[/TD]
[TD]09/04/18[/TD]
[/TR]
[TR]
[TD]204[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]205[/TD]
[TD]09/08/18[/TD]
[/TR]
[TR]
[TD]205[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I need to count records from September in the "Date" column and only when it's not repeated in the "Subscriber_ID" column. In this case, the correct answer will be 1, because is the only one that matches the condition. As you may see, subscriber_ID 203 it the only record that it's not repeated and also that the date falls within September. Subscribers 101 and 205 should not be considered since both are repeated, in spite of having a date that matches.

 

[Aladin Akyurek]

It worked great, thanks a lot!

You are welcome.

 

[rlobera]

What if I need to add multiple conditions? Would it be useful (possible) to use the sumproduct function? Used it other times but don’t really know how to use it here. Maybe I still need to use nested IFs. For example, if I have another column and the condition to count shall be if the corresponding cell is empty.

 

[Aladin Akyurek]

What if I need to add multiple conditions? Would it be useful (possible) to use the sumproduct function? Used it other times but don’t really know how to use it here. Maybe I still need to use nested IFs. For example, if I have another column and the condition to count shall be if the corresponding cell is empty.

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(A2:A8,A2:A8)=1,IF(B2:B8-DAY(B2:B8)+1=D1,IF(C2:C8="jon",IF(D2:D8="",1)))))


The following is equivalent:

=SUM(IF(FREQUENCY(A2:A8,A2:A8)=1,IF((B2:B8-DAY(B2:B8)+1=F1)*(C2:C8="jon")*(D2:D8=""),1)))

See also this link for more info: https://www.mrexcel.com/forum/excel-questions/292473-performance-formulas-unique-count.html

 

[rlobera]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(A2:A8,A2:A8)=1,IF(B2:B8-DAY(B2:B8)+1=D1,IF(C2:C8="jon",IF(D2:D8="",1)))))


The following is equivalent:

=SUM(IF(FREQUENCY(A2:A8,A2:A8)=1,IF((B2:B8-DAY(B2:B8)+1=F1)*(C2:C8="jon")*(D2:D8=""),1)))

See also this link for more info: https://www.mrexcel.com/forum/excel-questions/292473-performance-formulas-unique-count.html

Yes, I tried myself before your answer and it worked. Thanks again!

However, just digging within my dataset, I realized that there’s something wrong with the formula, because I forgot to consider something important when I posted this post. So, I have a new challenge for you, guys.

Sometimes there are 2 or more records for the same subscriber (the subscriber id will be repeated 2 or more times), and each record also has a date in the corresponding column. In this case, I just have to look for the record with the last date. Actually, as I’m writing this text, I realized that it doesn’t matter how many records are for the same subscriber, since what I have to count is the one with the last date, and if there is a record for the same subscriber where the date is empty, then I don’t have to count that subscriber at all.

Would you please help me? If you need a table with examples to better understand what I’m trying to say, please let me know.

 

[Aladin Akyurek]

Yes, I tried myself before your answer and it worked. Thanks again!

However, just digging within my dataset, I realized that there’s something wrong with the formula, because I forgot to consider something important when I posted this post. So, I have a new challenge for you, guys.

Sometimes there are 2 or more records for the same subscriber (the subscriber id will be repeated 2 or more times), and each record also has a date in the corresponding column. In this case, I just have to look for the record with the last date. Actually, as I’m writing this text, I realized that it doesn’t matter how many records are for the same subscriber, since what I have to count is the one with the last date, and if there is a record for the same subscriber where the date is empty, then I don’t have to count that subscriber at all.

Would you please help me? If you need a table with examples to better understand what I’m trying to say, please let me know.

Try to post a sample along with the relevant conditions which must hold and the expected result.

 

[rlobera]

Sure! This is a sample of the sheet that has the raw data that we need to work with:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]Customer[/TD]
[TD="align: center"]Subs ID[/TD]
[TD="align: center"]Lock-In Date[/TD]
[TD="align: center"]Lock-Out Date[/TD]
[TD="align: center"]Plan[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]6/7/18[/TD]
[TD="align: center"]6/14/18[/TD]
[TD="align: center"]Standard[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]3/20/18[/TD]
[TD="align: center"]6/7/18[/TD]
[TD="align: center"]Premium[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]101[/TD]
[TD="align: center"]3/22/18[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Premium[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]102[/TD]
[TD="align: center"]3/26/18[/TD]
[TD="align: center"]6/18/18[/TD]
[TD="align: center"]Premium[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]102[/TD]
[TD="align: center"]6/18/18[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Standard[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]103[/TD]
[TD="align: center"]5/14/18[/TD]
[TD="align: center"]6/12/18[/TD]
[TD="align: center"]Standard[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]104[/TD]
[TD="align: center"]4/5/18[/TD]
[TD="align: center"]6/6/18[/TD]
[TD="align: center"]Premium[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]104[/TD]
[TD="align: center"]6/6/18[/TD]
[TD="align: center"]6/25/18[/TD]
[TD="align: center"]Standard[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]104[/TD]
[TD="align: center"]6/25/18[/TD]
[TD="align: center"]8/23/18[/TD]
[TD="align: center"]Infinity[/TD]
[/TR]
</tbody>[/TABLE]

​

What I need is to count subscribers (Subs ID) from customer 1 (Customer) where the LAST lock-out date falls within June. In this case, the result should be 2, because:

- Subs ID 100 made a change of plan from Premium to Standard, and then he effectively unsubscribes on 6/14/18 (the change of plan does not count so, in this case, the result is 1, not 2).
- Subs ID 101 does not have a lock-out date, so this record doesn't matter.
- Subs ID 102 also made a change of plan from Premium to Standard, and he's still a subscriber, so this one does not count either.
- Subs ID 103 subscribes on 5/14/18 and unsubscribes on 6/12/18, so this one counts too.
- Finally, Subs ID 104 made two changes of plan, and then he effectively unsubscribes on 8/23/18. This one doesn't count either, since what we are looking for are the ones where the last lock-out date was in June, and the other records from this subscriber, where the lock-out date was in June, were just plan changes.

The result, as I previously mentioned, should be 2.

Hope you can help me.

Best.

 

[Aladin Akyurek]

Hard to follow. What is plan change? Care to indicate in F where a plan change occurs and where it doesn't?

 

[rlobera]

Hard to follow. What is plan change? Care to indicate in F where a plan change occurs and where it doesn't?

Plan change is when a subscriber decides to move from one type o service to another. In that case, there is a lock-out from the current plan (the ending date), and a new record is generated with a lock-in date (same as lock-out from previous plan) for the new plan. It remains that way (without a lock-out date for that record - empty cell) until the subscriber decides to change to another plan or unsubscribe from the service.

Hope this helps!

 

[Aladin Akyurek]

Plan change is when a subscriber decides to move from one type o service to another. In that case, there is a lock-out from the current plan (the ending date), and a new record is generated with a lock-in date (same as lock-out from previous plan) for the new plan. It remains that way (without a lock-out date for that record - empty cell) until the subscriber decides to change to another plan or unsubscribe from the service.

Hope this helps!

As I asked for, try to tag the column F accordingly and post the tags.

 

[rlobera]

As I asked for, try to tag the column F accordingly and post the tags.

So sorry!

I added a column to show the record ID (rows) and a column to indicate when a change of plan occurs:

[TABLE="class: cms_table_grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]Record ID[/TD]
[TD="align: center"]Customer[/TD]
[TD="align: center"]Subs ID[/TD]
[TD="align: center"]Lock-In Date[/TD]
[TD="align: center"]Lock-Out Date[/TD]
[TD="align: center"]Plan[/TD]
[TD="align: center"]Change of Plan[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]3/20/18[/TD]
[TD="align: center"]6/7/18[/TD]
[TD="align: center"]Standard[/TD]
[TD="align: center"]From record #1 to #2[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]6/7/18[/TD]
[TD="align: center"]6/14/18[/TD]
[TD="align: center"]Premium[/TD]
[TD="align: center"]Unsubscribed on 6/14/18[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]101[/TD]
[TD="align: center"]3/22/18[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Premium[/TD]
[TD="align: center"]Still subscribed[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]102[/TD]
[TD="align: center"]3/26/18[/TD]
[TD="align: center"]6/18/18[/TD]
[TD="align: center"]Premium[/TD]
[TD="align: center"]From record #4 to #5[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]102[/TD]
[TD="align: center"]6/18/18[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Standard[/TD]
[TD="align: center"]Still subscribed[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]103[/TD]
[TD="align: center"]5/14/18[/TD]
[TD="align: center"]6/12/18[/TD]
[TD="align: center"]Standard[/TD]
[TD="align: center"]Unsubscribed on 6/12/18[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]104[/TD]
[TD="align: center"]4/5/18[/TD]
[TD="align: center"]6/6/18[/TD]
[TD="align: center"]Premium[/TD]
[TD="align: center"]From record #7 to #8[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]104[/TD]
[TD="align: center"]6/6/18[/TD]
[TD="align: center"]6/25/18[/TD]
[TD="align: center"]Standard[/TD]
[TD="align: center"]From record #8 to #9[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]104[/TD]
[TD="align: center"]6/25/18[/TD]
[TD="align: center"]8/23/18[/TD]
[TD="align: center"]Infinity[/TD]
[TD="align: center"]Unsubscribed on 8/23/18[/TD]
[/TR]
</tbody>[/TABLE]

Thank you!