# Count unique number in column B for specified month.

#### bentxoxo

##### New Member
Hi All,

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

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)

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

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### lrobbo314

##### Well-known Member
You can do this with Power Query.

Code:
``````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``````
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Auto-Num</td><td style="color: #333333;;">Test-Count</td><td style=";">Date</td><td style="text-align: right;;"></td><td style=";">Month</td><td style=";">Unique-Count</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;color: #333333;;">1</td><td style="text-align: right;;">4440</td><td style="text-align: right;;">9/25/2019</td><td style="text-align: right;;"></td><td style=";">Sep</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;color: #333333;;">4440</td><td style="text-align: right;;">9/25/2019</td><td style="text-align: right;;"></td><td style=";">Oct</td><td style="text-align: right;;">7</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;color: #333333;;">3</td><td style="text-align: right;;">4441</td><td style="text-align: right;;">9/28/2019</td><td style="text-align: right;;"></td><td style=";">Jan</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;color: #333333;;">4</td><td style="text-align: right;;">4442</td><td style="text-align: right;;">9/29/2019</td><td style="text-align: right;;"></td><td style=";">May</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;color: #333333;;">5</td><td style="text-align: right;;">4443</td><td style="text-align: right;;">9/30/2019</td><td style="text-align: right;;"></td><td style=";">Nov</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;color: #333333;;">6</td><td style="text-align: right;;">4444</td><td style="text-align: right;;">10/1/2019</td><td style="text-align: right;;"></td><td style=";">Aug</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">7</td><td style="text-align: right;color: #333333;;">4444</td><td style="text-align: right;;">1/10/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;color: #333333;;">8</td><td style="text-align: right;;">4445</td><td style="text-align: right;;">10/3/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;color: #333333;;">9</td><td style="text-align: right;;">4446</td><td style="text-align: right;;">10/5/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">10</td><td style="text-align: right;color: #333333;;">4446</td><td style="text-align: right;;">5/10/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">11</td><td style="text-align: right;color: #333333;;">4446</td><td style="text-align: right;;">5/10/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">12</td><td style="text-align: right;color: #333333;;">4446</td><td style="text-align: right;;">5/10/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;color: #333333;;">13</td><td style="text-align: right;;">4447</td><td style="text-align: right;;">10/10/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;">14</td><td style="text-align: right;color: #333333;;">4447</td><td style="text-align: right;;">10/10/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;color: #333333;;">15</td><td style="text-align: right;;">4448</td><td style="text-align: right;;">10/15/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;color: #333333;;">16</td><td style="text-align: right;;">4449</td><td style="text-align: right;;">10/20/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="text-align: right;color: #333333;;">17</td><td style="text-align: right;;">4450</td><td style="text-align: right;;">10/30/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style="text-align: right;color: #333333;;">18</td><td style="text-align: right;;">4451</td><td style="text-align: right;;">11/2/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style="text-align: right;color: #333333;;">19</td><td style="text-align: right;;">4452</td><td style="text-align: right;;">11/4/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style="text-align: right;color: #333333;;">20</td><td style="text-align: right;;">4453</td><td style="text-align: right;;">11/8/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style="text-align: right;;">21</td><td style="text-align: right;color: #333333;;">4453</td><td style="text-align: right;;">8/11/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style="text-align: right;color: #333333;;">22</td><td style="text-align: right;;">4454</td><td style="text-align: right;;">11/9/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style="text-align: right;color: #333333;;">23</td><td style="text-align: right;;">4456</td><td style="text-align: right;;">11/10/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br />

#### DanteAmor

##### Well-known Member
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

##### New Member
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

##### New Member
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

##### New Member
Hi DanteAmor,

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

Many thanks,

#### bentxoxo

##### New Member
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))}

#### DanteAmor

##### Well-known Member
Something like this:

Book1
ABCDEFG
1NumCategoryDateMonthResult
24440125/09/201992
34440225/09/2019104
44441328/09/2019112
54442129/09/2019
64442230/09/2019
74444301/10/2019
84444101/10/2019
94445203/10/2019
104446305/10/2019
114446105/10/2019
124446205/10/2019
134446305/10/2019
144447110/10/2019
154447210/10/2019
164448315/10/2019
174449120/10/2019
184450230/10/2019
194451302/11/2019
204452104/11/2019
214453208/11/2019
224453308/11/2019
234454109/11/2019
244456210/11/2019
Sheet
Cell Formulas
RangeFormula
G2:G4G2{=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

##### New Member

Formula worked well and thank you for your time.

Regards,