Count unique number in column B for specified month.

bentxoxo

New Member
Joined
Jan 13, 2016
Messages
29
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!
 

Some videos you may like

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
Joined
Jul 14, 2008
Messages
2,688
Office Version
365, 2019, 2016
Platform
Windows
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
Joined
Dec 3, 2018
Messages
10,219
Office Version
2007
Platform
Windows
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
Joined
Jan 13, 2016
Messages
29
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
Joined
Jan 13, 2016
Messages
29
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,
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,219
Office Version
2007
Platform
Windows
I'm glad to help you. Thanks for the feedback.
 

bentxoxo

New Member
Joined
Jan 13, 2016
Messages
29
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

Well-known Member
Joined
Dec 3, 2018
Messages
10,219
Office Version
2007
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,089,850
Messages
5,410,751
Members
403,328
Latest member
dalan

This Week's Hot Topics

Top