# Count Unique Values based on multiple criterias

1. ## Count Unique Values based on multiple criterias

Hi Excel Experts,

I found this awesome formula from below URL:-

http://www.mrexcel.com/forum/excel-q...nge-cells.html

The contributed formula counts how many unique values in rows and omits errors and blanks. However, this only applicable for one column. What I'm trying to achieve is something extra. I need to calculate how many Users (based on User ID) that access a system on the same date. So, my count must consider the date as well before populates output. Below is my data:-

UserID Date
V03751X 14/3/2016
V03751X 15/3/2016
V04046X 14/3/2016
V03658X 15/3/2016
V03751X 14/3/2016
V03752X 14/3/2016

So, on date 14/3/2016, the result should be 3 and on 15/3/2016 should be 2.

I tried below, but returned error or '0' :-

=SUM(IF(1-ISERROR(\$J\$16:\$J\$21),IF(\$J\$16:\$J\$21<>"",1))/COUNTIFS(\$K\$16:\$K\$21,\$K21,\$J\$16:\$J\$21,\$J\$16:\$J\$21&""))
with Ctrl+Shift+Enter

=SUM(IF(FREQUENCY(IF(1-ISERROR(\$J\$16:\$J\$21),IF(\$J\$16:\$J\$21<>"",AND(MATCH(K16,\$K\$16:\$K\$21,0),MATCH("~"&\$J\$16:\$J\$21,\$J\$16:\$J\$21&"",0)))),ROW(\$J\$16:\$J\$21)-ROW(\$J\$16)+1),1))
with Ctrl+Shift+Enter

DZ

2. ## Re: Count Unique Values based on multiple criterias

Try this one with Ctrl Shift Enter.

=SUM(IFERROR(1/COUNTIFS(\$J\$16:\$J\$21,\$J\$16:\$J\$21&"",\$K\$16:\$K\$21,\$K\$16:\$K\$21,\$K\$16:\$K\$21,\$K21),0))

3. ## Re: Count Unique Values based on multiple criterias

Jason:

typo in Formula ??

=SUM(IFERROR(1/COUNTIFS(\$J\$16:\$J\$21,\$J\$16:\$J\$21&"",\$K\$16:\$K\$21,\$K\$16:\$K\$21,\$K\$16:\$K\$21,\$K16),0))

4. ## Re: Count Unique Values based on multiple criterias

Should work either way, John.

I based mine on the criteria in the OP's first formula, where it looks like you based yours on the second.

Hopefully at least one of us has it right.

5. ## Re: Count Unique Values based on multiple criterias

Jason,
You are right: when I tried it, I put the formula in K16 and dragged down so K21 was incremented and naturally gave wrong result! My apologies.

John

6. ## Re: Count Unique Values based on multiple criterias

Hi http://www.excelforum.com/member.php?u=913270

I've tested your formula. It works awesomely if there is no data error or blanks between the rows. So, I added a little bit and came out with the below:-

with Ctrl+Shift+Enter

=SUM(IFERROR(IF(1-ISERROR(\$J\$16:\$J\$21),IF(\$J\$16:\$J\$21<>"",1))/COUNTIFS(\$J\$16:\$J\$21,\$J\$16:\$J\$21&"",\$K\$16:\$K\$21,\$K\$16:\$K\$21,\$K\$16:\$K\$21,\$K16),0))

Thank you so much for your time and expertise, JohnTopley!

7. ## Re: Count Unique Values based on multiple criterias

@Dahlia,
Thanks are due to Jason not me.

8. ## Re: Count Unique Values based on multiple criterias

Originally Posted by JohnTopley
Jason,
You are right: when I tried it, I put the formula in K16 and dragged down so K21 was incremented and naturally gave wrong result! My apologies.

John
No need to apologise, John. On the assumption that dragging down is a requirement, you got it right, not me.

@Dahlia

Assuming that the errors or blanks would be in column J, with a date in the same row of column K (this was the only thing I found which made the original suggestion fail).

Give this one a go, Shift Ctrl Enter again.

=SUM(IFERROR(SIGN(LEN(\$J\$16:\$J\$23))/COUNTIFS(\$J\$16:\$J\$23,\$J\$16:\$J\$23&"",\$K\$16:\$K\$23,\$K\$16:\$K\$23,\$K\$16:\$K\$23,\$K16),0))

It looks like it works correctly, but I've only done a quick test.

9. ## Re: Count Unique Values based on multiple criterias

Hi jason.b75,

Your formula works perfectly! Neat, short and concise! Tqvm!! Yeayy!

There are currently 1 users browsing this thread. (0 members and 1 guests)