Count unique values with condition

R

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.
 
Sort by date Sort by votes

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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
rlobera said:
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.
Click to expand...

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
Aladin Akyurek said:
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
Click to expand...

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
rlobera said:
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.
Click to expand...

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
Aladin Akyurek said:
Hard to follow. What is plan change? Care to indicate in F where a plan change occurs and where it doesn't?
Click to expand...

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
rlobera said:
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!
Click to expand...

As I asked for, try to tag the column F accordingly and post the tags.
 
Upvote 0
Aladin Akyurek said:
As I asked for, try to tag the column F accordingly and post the tags.
Click to expand...

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
You must log in or register to reply here.

Similar threads

N
  • Question Question
Help creating a formula
2
Replies
11
Views
494
Fluff
Fluff
M
  • Question Question
How to sum cells in a column that match an End of Month date in an adjacent column
Replies
7
Views
244
Peter_SSs
Peter_SSs
T
  • Solved
Looking for VBA Code to Conditionally Format Time Tracker on Entry
Replies
3
Views
177
Tara_A_Data
T
S
  • Question Question
Conditional Formatting based on passed date
Replies
9
Views
435
SnKeaK
S
R
  • Question Question
Trying to find start and end times in a date/time column with a difficult twist.
Replies
8
Views
395
felixstraube
F

Forum statistics

Threads
1,223,262
Messages
6,171,080
Members
452,377
Latest member
bradfordsam

Share this page

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