Count number of unique values in specified date range:

[bentxoxo]

Hi


I'm looking to calculate on how many RNA issued in specified month. Please see my example below:



Thanks in advance!

Date		RNA					TOTAL
18/02/2019		3371	1				RNA
18/02/2019		3372	2		Month		ISSUED
18/02/2019		3373	3		Feb		18
18/02/2019		3374	4		Mar		19
18/02/2019		3374
18/02/2019		3374
18/02/2019		3374
18/02/2019		3375	5
18/02/2019		3375
18/02/2019		3376	6
20/02/2019		3377	7
20/02/2019		3377
20/02/2019		3378	8
21/02/2019		3379	9
21/02/2019		3380	10
22/02/2019		3381	11
22/02/2019		3381
22/02/2019		3381
22/02/2019		3381
22/02/2019		3381
22/02/2019		3381
22/02/2019		3381
22/02/2019		3382	12
26/02/2019		3383	13
27/02/2019		3384	14
28/02/2019		3385	15
28/02/2019		3386	16
28/02/2019		3387	17
28/02/2019		3388	18
1/03/2019		3389	1
1/03/2019		3390	2
1/03/2019		3391	3
1/03/2019		3392	4
1/03/2019		3393	5
1/03/2019		3394	6
6/03/2019		3395	7
6/03/2019		3396	8
6/03/2019		3397	9
6/03/2019		3398	10
8/03/2019		3399	11
8/03/2019		3400	12
8/03/2019		3401	13
8/03/2019		3402	14
8/03/2019		4000	15
15/03/2019		4001	16
15/03/2019		4001
15/03/2019		4001
15/03/2019		4002	17
18/03/2019		4003	18
18/03/2019		4004	19
18/03/2019		4004

<tbody>
</tbody>

 

[sandy666]

with PowerQuery aka Get&Transform:

Date	RNA		Date	Count
18/02/2019​	3371​		February	18​
18/02/2019​	3372​		March	19​
18/02/2019​	3373​
18/02/2019​	3374​
18/02/2019​	3374​
18/02/2019​	3374​
18/02/2019​	3374​
18/02/2019​	3375​
18/02/2019​	3375​
18/02/2019​	3376​
20/02/2019​	3377​
20/02/2019​	3377​
20/02/2019​	3378​
21/02/2019​	3379​
21/02/2019​	3380​
22/02/2019​	3381​
22/02/2019​	3381​
22/02/2019​	3381​
22/02/2019​	3381​
22/02/2019​	3381​
22/02/2019​	3381​
22/02/2019​	3381​
22/02/2019​	3382​
26/02/2019​	3383​
27/02/2019​	3384​
28/02/2019​	3385​
28/02/2019​	3386​
28/02/2019​	3387​
28/02/2019​	3388​
01/03/2019​	3389​
01/03/2019​	3390​
01/03/2019​	3391​
01/03/2019​	3392​
01/03/2019​	3393​
01/03/2019​	3394​
06/03/2019​	3395​
06/03/2019​	3396​
06/03/2019​	3397​
06/03/2019​	3398​
08/03/2019​	3399​
08/03/2019​	3400​
08/03/2019​	3401​
08/03/2019​	3402​
08/03/2019​	4000​
15/03/2019​	4001​
15/03/2019​	4001​
15/03/2019​	4001​
15/03/2019​	4002​
18/03/2019​	4003​
18/03/2019​	4004​
18/03/2019​	4004​

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Month = Table.TransformColumns(Source, {{"Date", each Date.MonthName(_), type text}}),
    RemDup = Table.Distinct(Month),
    Group = Table.Group(RemDup, {"Date"}, {{"Count", each Table.RowCount(_), type number}})
in
    Group[/SIZE]

 

[Aladin Akyurek]

In H4 control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($A$2:$A$52-DAY($A$2:$A$52)+1=(1&F4)+0,$C$2:$C$52),$C$2:$C$52),1))

where F4 = Feb.

 

[bentxoxo]

Beautiful!

Thanks you so much Sandy666 and Aladin Akyurek.

Both macro and formula worked like charm - Much appreciated!

 

[sandy666]

You are welcome

btw. this is not a macro but M-code

Have a nice day

 

[bentxoxo]

Thanks Sandy666!

How can I add another condition in formula.

Where F4 = FEB and G4 = "1. Pack Damaged on Arrival"

Thanks heaps!

 

[Aladin Akyurek]

Thanks Sandy666!

How can I add another condition in formula.

Where F4 = FEB and G4 = "1. Pack Damaged on Arrival"

Thanks heaps!

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($A$2:$A$52-DAY($A$2:$A$52)+1=(1&F4)+0,IF(DamageRange=G4,$C$2:$C$52)),$C$2:$C$52),1))
​

 

[bentxoxo]

Ripper - Thanks Mate!

 

[Aladin Akyurek]

Ripper - Thanks Mate!

You are welcome.