Count unique values with condition

rlobera

New Member
Joined
Mar 9, 2017
Messages
38
Office Version
  1. 2019
Platform
  1. MacOS
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.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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!
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top