Hello - I'm trying to count distinct ids per month. but it is not working for me.
what I'm trying to do is get the count of distinct ID per month. looking for the following output:
Nov 2017 3
Dec 2017 1
Jan 2018 0
so far i have:
it gives me 4. that is right but now i need it monthly.
as following:
Nov 2017 3
Dec 2017 1
Jan 2018 0
i also tried the following but i'm confused how to integrate distinct ID win it.
Any help is appreciated. Thanks.
Code:
worksheet1
A B C
1 ID tempDate DateValidation
2 12 11/12/2017 11/01/2017
3 14 12/23/2017 12/01/2017
4 16 11/17/2017 01/01/2017
5 20 11/11/2017
6 12 11/16/2017
7 16 11/01/2017
8 14 12/01/2017
what I'm trying to do is get the count of distinct ID per month. looking for the following output:
Nov 2017 3
Dec 2017 1
Jan 2018 0
so far i have:
Code:
=SUM(IF(FREQUENCY(A2:A8, A2:A8)>0,1))
it gives me 4. that is right but now i need it monthly.
as following:
Nov 2017 3
Dec 2017 1
Jan 2018 0
i also tried the following but i'm confused how to integrate distinct ID win it.
Code:
=IFERROR(COUNTIFS($B$2:$B$8,">="&$C$2, $B$2:$B$8,"<="&EOMONTH($C$2,0)),"")
Any help is appreciated. Thanks.