Trying to select distinct values in date column

[abush]

Ive been trying to count distinct values in column A (date) based on a selection in C1 in this case November i.e 11th month of year
The below works nicely in returning just the data associated with November (11)
I have been using the below where C1&"*" is equal to the number of month
=COUNTIFS('Detailed transactions cleaned'!E2:E268,">=12:00:01 AM",'Detailed transactions cleaned'!E2:E268,"<=12:00:10 AM",'Detailed transactions cleaned'!B2:B268, "inbound",'Detailed transactions cleaned'!A2:A268,C1&"*")
The problem occurs when I try to do a selection of distinct count of days in column A
This below returns 3 days since it includes October 31 i.e. 10 month of year and November 1,2 (see table above)
I would want to select C1 = 11 and only return November i.e. 2 days November 1,2 if I select C1 = 10 the 1 day Oct 31

=SUMPRODUCT(('Detailed transactions cleaned'!A2:A268<>"") / COUNTIF('Detailed transactions cleaned'!A2:A268,'Detailed transactions cleaned'!A2:A268 & ""))
what I want is something that would be
=SUMPRODUCT(('Detailed transactions cleaned'!A2:A268<>"") / COUNTIF('Detailed transactions cleaned'!A2:A268,'Detailed transactions cleaned'!A2:A268 & ""))
and constrained by
=COUNTIFS('Detailed transactions cleaned'!E2:E268,">=12:00:01 AM",'Detailed transactions cleaned'!E2:E268,"<=12:00:10 AM",'Detailed transactions cleaned'!B2:B268, "inbound",'Detailed transactions cleaned'!A2:A268,C1&"*")

[Tony Valko]

Maybe something like this...

Data Range

	A	B	C	D
1	10/28/2016	------	Month	Count
2	10/28/2016		11	3
3	10/31/2016
4	11/1/2016
5	11/1/2016
6	11/1/2016
7	11/2/2016
8	11/3/2016
9	11/3/2016
10	11/3/2016

This array formula** entered in D2:

=SUM(IF(FREQUENCY(IF(MONTH(A1:A10)=C2,A1:A10),A1:A10),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[abush]

Well that was awesome. Good work!! Honestly a clear simple solution!!.
I came across 2 issues
1. never knew the ctl shift trick
2. My dates in my source was text so I had to convert it to a true date =DATEVALUE(A2)


Thanks!!!!

[Tony Valko]

You're welcome. Thanks for the feedback!

[abush]

Well I'll push my luck If I wanted to incorporate another IF condition both must be true like month 10 and day 3 how would you recommend.
I want to count unique month and day like all the calls in october on the 3rd day c2=10 D2=3
I tried below no luck
=SUM(IF(FREQUENCY(IF(AND(MONTH(A1:A10)=C2,A1:A10),(DAY(A1:A10)=D2,A1:A10)),A1:A10),1))

Cheers
Adam

[Tony Valko]

The other formula counted unique dates for the month.

This request is a bit unusual. You want to count unique 3rd of Octobers? See where I'm going with this?

There can only be 1 unique combination of October 3rd.

[abush]

Your right
what I actually needed was to count days where calls in October that were inbound something like
below where in my data set below there would be 2 days of inbound calls 10/13/2016 should not be counted
Thanks!


=SUMPRODUCT(IF(FREQUENCY(IF(AND(MONTH('Detailed transactions cleaned'!H2:H268)=C1,
'Detailed transactions cleaned'!C2:C268="inbound"),
'Detailed transactions cleaned'!H2:H268),1))

10/12/2016 inbound 0:12:23 0:00:06
10/12/2016 inbound 0:06:45 0:00:05
10/12/2016 inbound 0:03:55 0:00:08
10/12/2016 outbound 0:01:37 0:00:12
10/13/2016 outbound 0:02:04 0:00:24
10/14/2016 outbound 0:01:49 0:00:06
10/14/2016 inbound 0:00:00 0:00:00
10/14/2016 inbound 0:00:00 0:00:00

[Tony Valko]

Try this array formula**:

=SUM(IF(FREQUENCY(IF(MONTH('Detailed transactions cleaned'!H2:H268)=C1,IF('Detailed transactions cleaned'!C2:C268="inbound",'Detailed transactions cleaned'!H2:H268)),'Detailed transactions cleaned'!H2:H268),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[abush]

Tony:
Yup worked like a charm! Its the nesting of the IF statements in excel that just kills me for some reason.
I also used a brute force way of counting inbound for each day
Count all calls inbound for each day of month then count each day greater than 0 not as elegant as yours

=COUNTIFS('Detailed transactions cleaned'!$F$2:F$268,">12:00:01 AM",
'Detailed transactions cleaned'!$C$2:$C$268,"inbound",
'Detailed transactions cleaned'!$A$2:$A$268,$J$2&"*",
'Detailed transactions cleaned'!$A$2:$A$268,"*/"&K3&"/*")

=COUNTIFS('Detailed transactions cleaned'!$N$2:$N$33,
">0")

WOW again thanks. I believe this is the only forum of its kind where if you ask a question it gets answered.
Not the typical question/answers of "Why would you want to count X?" or "Why are you using excel... Python or Perl has better ways of doing X"

Again Thanks!!!!

[Tony Valko]

You're welcome. Thanks for the feedback!