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

Appreciate your expertise.

Thank you in advance.

DZ

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))

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))

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.

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

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!

@Dahlia,
Thanks are due to Jason not me.

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.

Hi jason.b75,

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