Count Unique Values based on multiple criterias

[dellzy]

Hi Excel Experts,

I found this awesome formula from below URL:-

http://www.mrexcel.com/forum/excel-...e-non-error-non-blank-values-range-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:-

[TABLE="class: grid, width: 200, align: left"]
<tbody>[TR]
[TD]UserID[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]V03751X[/TD]
[TD]14/3/2016[/TD]
[/TR]
[TR]
[TD]V03751X[/TD]
[TD]15/3/2016[/TD]
[/TR]
[TR]
[TD]V04046X[/TD]
[TD]14/3/2016[/TD]
[/TR]
[TR]
[TD]V03658X[/TD]
[TD]15/3/2016[/TD]
[/TR]
[TR]
[TD]V03751X[/TD]
[TD]14/3/2016[/TD]
[/TR]
[TR]
[TD]V03752X[/TD]
[TD]14/3/2016[/TD]
[/TR]
</tbody>[/TABLE]
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

 

[dellzy]

Hi,

Please ignore this. Got the solution as 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))

 

[radian89]

Hi Dellzy,

simple alternatives, add helper column called checker

[TABLE="width: 500"]
<tbody>[TR]
[TD]userID[/TD]
[TD]Date[/TD]
[TD]Checker[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Checker :
=if(or(iserror(UserID)=true,isblank(userID)=true), FALSE, TRUE)&Date

for the count :
=countif(Checker, TRUE&yourdate)

thanks

 

[Mike Szczesny]

Hi dellzy,

Perhaps you can use something like this.
Mike Szczesny

Excel 2012
	A	B	C
1	1		5
2
3	2
4	3
5	1
6	2
7	7
8	#DIV/0!
9	8
Sheet1

Cell Formulas
Range	Formula
A8	=3/0
C1	{=SUM(IF(FREQUENCY(IF(1-ISERROR($A$1:$A$9),IF($A$1:$A$9<>"",MATCH($A$1:$A$9,$A$1:$A$9,0))),ROW($A$1:$A$9)-ROW($A$1)+1),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.