Counting unique entries with two given conditions

[rogerm]

Could anyone help me with the following:

I have three colums of data.

In the first colum I have names
The second Column I have week numbers
And the 3rd column I have dates

I have to report how many dates in ta given week number reports were submitted by each person.

The problem is that on some days more than one report has been submitted so when I count using an array formula such as

=SUM((A2:A10="Name")*(B2:B10="week number")
*C2:C10)
The result is wrong.

I need to count the number of distinct (unique) dates in the date column (c)for a given person on a given date

so I need a combination of

=SUM((A2:A10="Name")*(B2:B10="week number")
to select the ranges together with

=SUM(N(FREQUENCY(C1:A10,C1:C10)>0))

but I'm not sure how to put them together.

to count the unique entries for the range.

Or perhaps there is an easier way or another function I could use.

Any suggestions would be gratefully received.

 

[leibale]

Count Uniques with 2 conditions

Array-enter:

=SUM(IF((A2:A5=E1)*(B2:B5=F1),1/COUNTIF(C2:C5,C2:C5)))

where E1 houses a name and F1 a week number.

To array-enter a formula, hit control+shift+enter at the same time, not just enter.

Aladin

are you sure about this solution?

 

[leibale]

please look at this examplel:

2006_03_07.xls
	A	B	C	D	E	F	G	H
1
2
3	month	day	carID
4	jan	1	11
5	jan	1	12
6	jan	2	11
7	jan	2	11			month	jan
8	jan	2	12			day	2
9	jan	3	11
10	jan	3	12			0.452381
11	feb	1	11
12	feb	1	11
13	feb	1	12
14	feb	2	12
15	feb	2	12
16	feb	2	11
17
18
19
sheet1

 

[Domenic]

Re: Count Uniques with 2 conditions

are you sure about this solution?

Since the ID is not unique to the 'Month' and 'Day', try...

=COUNT(1/FREQUENCY(IF(A4:A16=G7,IF(B4:B16=G8,C4:C16)),IF(A4:A16=G7,IF(B4:B16=G8,C4:C16))))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[Aladin Akyurek]

please look at this examplel:
...

Some options...

[1]

=SUMPRODUCT(--($A$4:$A$16=G7),--($B$4:$B$16=G8),--($C$4:$C$16<>""),--(MATCH($A$4:$A$16&"#"&$B$4:$B$16&"#"&$C$4:$C$16,$A$4:$A$16&"#"&$B$4:$B$16&"#"&$C$4:$C$16,0)=ROW($C$4:$C$16)-ROW($C$4)+1))

[2] if you have the latest version of the morefunc.xll add-in installed:

=COUNTDIFF(IF($A$4:$A$16=G7,IF($B$4:$B$16=G8,$C$4:$C$16&"",""),""),,"")

which needs to be confirmed with control+shift+enter, not just with enter.

 

[leibale]

Thank you Domenic and Aladin for your kindness
I will check it later

why I can't see the sheet in my msg in this form, and I see instead the script of it?

 

[Aladin Akyurek]

...

why I can't see the sheet in my msg in this form, and I see instead the script of it?

I believe you need to activate the Html option in your profile at mrexcel.