Count unique number in column B for specified month.

[bentxoxo]

Hi All,


I'm wondering if someone could help me on this!


Is it possible to add month instead of range.


Like count unique number in column B for the month of Sep, Oct or Nov. Note that I have column C which has dated when claim number was issued.

A B
Test-Count | Auto-Num | Date
1 4440 25/09/2019
4440 25/09/2019
2 4441 28/09/2019
3 4442 29/09/2019
4 4443 30/09/2019
5 4444 01/10/2019
4444 01/10/2019
6 4445 03/10/2019
7 4446 05/10/2019
4446 05/10/2019
4446 05/10/2019
4446 05/10/2019
8 4447 10/10/2019
4447 10/10/2019
9 4448 15/10/2019
10 4449 20/10/2019
11 4450 30/10/2019
12 4451 02/11/2019
13 4452 04/11/2019
14 4453 08/11/2019
4453 08/11/2019
15 4454 09/11/2019
16 4456 10/11/2019



'=SUMPRODUCT(--(FREQUENCY(MATCH(B2:B24,B2:B24,0),ROW(B2:B24)-ROW(B2)+1)>0))


Above formula result: 16 (unique number in specified range - which is correct)

How can I add month instead of range?

Required Result:
Month of Sep: unique numbers: 4
Month of Oct: unique numbers: 7
Month of Nov: unique numbers: 5


Thanks in advance!

 

[lrobbo314]

You can do this with Power Query.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Auto-Num", Int64.Type}, {"Test-Count", Int64.Type}}),
    MonthName = Table.AddColumn(Type, "Month", each Date.ToText([Date],"MMM")),
    Group = Table.Group(MonthName, {"Month"}, {{"Count", each _, type table}}),
    GetUniqieCounts = Table.AddColumn(Group, "Unique-Count", each List.Count(List.Distinct(Table.Column([Count],"Test-Count"))))
in
    GetUniqieCounts

Book1
	A	B	C	D	E	F
1	Auto-Num	Test-Count	Date		Month	Unique-Count
2	1	4440	9/25/2019		Sep	4
3	2	4440	9/25/2019		Oct	7
4	3	4441	9/28/2019		Jan	1
5	4	4442	9/29/2019		May	1
6	5	4443	9/30/2019		Nov	5
7	6	4444	10/1/2019		Aug	1
8	7	4444	1/10/2019
9	8	4445	10/3/2019
10	9	4446	10/5/2019
11	10	4446	5/10/2019
12	11	4446	5/10/2019
13	12	4446	5/10/2019
14	13	4447	10/10/2019
15	14	4447	10/10/2019
16	15	4448	10/15/2019
17	16	4449	10/20/2019
18	17	4450	10/30/2019
19	18	4451	11/2/2019
20	19	4452	11/4/2019
21	20	4453	11/8/2019
22	21	4453	8/11/2019
23	22	4454	11/9/2019
24	23	4456	11/10/2019
Sheet1

 

[DanteAmor]

Try this

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:39.92px;" /><col style="width:76.04px;" /><col style="width:85.54px;" /><col style="width:40.87px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#92d050; font-weight:bold; "> </td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Num</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Date</td><td style="background-color:#92d050; font-weight:bold; "> </td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Month</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Result</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td style="text-align:right; ">4440</td><td style="text-align:right; ">25/09/2019</td><td > </td><td style="text-align:right; ">9</td><td style="text-align:right; ">4</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td style="text-align:right; ">4440</td><td style="text-align:right; ">25/09/2019</td><td > </td><td style="text-align:right; ">10</td><td style="text-align:right; ">7</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td style="text-align:right; ">4441</td><td style="text-align:right; ">28/09/2019</td><td > </td><td style="text-align:right; ">11</td><td style="text-align:right; ">5</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td style="text-align:right; ">4442</td><td style="text-align:right; ">29/09/2019</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td style="text-align:right; ">4443</td><td style="text-align:right; ">30/09/2019</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td style="text-align:right; ">4444</td><td style="text-align:right; ">01/10/2019</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td style="text-align:right; ">4444</td><td style="text-align:right; ">01/10/2019</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td style="text-align:right; ">4445</td><td style="text-align:right; ">03/10/2019</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td style="text-align:right; ">4446</td><td style="text-align:right; ">05/10/2019</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td style="text-align:right; ">4446</td><td style="text-align:right; ">05/10/2019</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td style="text-align:right; ">4446</td><td style="text-align:right; ">05/10/2019</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td > </td><td style="text-align:right; ">4446</td><td style="text-align:right; ">05/10/2019</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td > </td><td style="text-align:right; ">4447</td><td style="text-align:right; ">10/10/2019</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td > </td><td style="text-align:right; ">4447</td><td style="text-align:right; ">10/10/2019</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td > </td><td style="text-align:right; ">4448</td><td style="text-align:right; ">15/10/2019</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td > </td><td style="text-align:right; ">4449</td><td style="text-align:right; ">20/10/2019</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td > </td><td style="text-align:right; ">4450</td><td style="text-align:right; ">30/10/2019</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td > </td><td style="text-align:right; ">4451</td><td style="text-align:right; ">02/11/2019</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td > </td><td style="text-align:right; ">4452</td><td style="text-align:right; ">04/11/2019</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td > </td><td style="text-align:right; ">4453</td><td style="text-align:right; ">08/11/2019</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td > </td><td style="text-align:right; ">4453</td><td style="text-align:right; ">08/11/2019</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td > </td><td style="text-align:right; ">4454</td><td style="text-align:right; ">09/11/2019</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td > </td><td style="text-align:right; ">4456</td><td style="text-align:right; ">10/11/2019</td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >F2</td><td >{=COUNT(IF(FREQUENCY(IF(MONTH($C$2:$C$24)=E2,MATCH($B$2:$B$24,$B$2:$B$24,0)),ROW($B$2:$B$24)-ROW($B$2)+1),1))}</td></tr></table></td></tr></table>

 

[bentxoxo]

Hi Irobbo314,

Thank you for your reply and Array formula means the formula I was using or is there any other formula.

Am sorry, after I paste code where to get array formula?

Regards,

 

[bentxoxo]

Hi DanteAmor,

Thank you for your reply. I tried this formula but am unable to get proper results.

I changed column B format as date 01/12/2019 like dd/mm/yyyy. Also tried column E as mm only or number only but still can't quite get the result.

Is there any thing else I need to check.

Thanks,

 

[bentxoxo]

Hi DanteAmor,

Thank it's all working now used array formula option.

Many thanks,

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[bentxoxo]

Good Morning All,

Thank you for your earlier help the below formula works perfectly and is it possible to add another condition for example Count unique number in column B for specified month and where column C = 1. Note column "C" always have number 1 to 10 which actually specify category of claim.

{=COUNT(IF(FREQUENCY(IF(MONTH($C$2:$C$24)=E2,MATCH($B$2:$B$24,$B$2:$B$24,0)),ROW($B$2:$B$24)-ROW($B$2)+1),1))}

Many thanks in advance.

 

[DanteAmor]

Something like this:

Book1
	A	B	C	D	E	F	G
1		Num	Category	Date		Month	Result
2		4440	1	25/09/2019		9	2
3		4440	2	25/09/2019		10	4
4		4441	3	28/09/2019		11	2
5		4442	1	29/09/2019
6		4442	2	30/09/2019
7		4444	3	01/10/2019
8		4444	1	01/10/2019
9		4445	2	03/10/2019
10		4446	3	05/10/2019
11		4446	1	05/10/2019
12		4446	2	05/10/2019
13		4446	3	05/10/2019
14		4447	1	10/10/2019
15		4447	2	10/10/2019
16		4448	3	15/10/2019
17		4449	1	20/10/2019
18		4450	2	30/10/2019
19		4451	3	02/11/2019
20		4452	1	04/11/2019
21		4453	2	08/11/2019
22		4453	3	08/11/2019
23		4454	1	09/11/2019
24		4456	2	10/11/2019
Sheet

Cell Formulas
Range	Formula
G2:G4	G2	{=COUNT(IF(FREQUENCY(IF(MONTH($D$2:$D$24)=F2,IF($C$2:$C$24=1,MATCH($B$2:$B$24,$B$2:$B$24,0))),ROW($B$2:$B$24)-ROW($B$2)+1),1))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[bentxoxo]

You're always very helpful DanateAmor.

Formula worked well and thank you for your time.

Regards,