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!
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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


Book1
ABCDEF
1Auto-NumTest-CountDateMonthUnique-Count
2144409/25/2019Sep4
3244409/25/2019Oct7
4344419/28/2019Jan1
5444429/29/2019May1
6544439/30/2019Nov5
76444410/1/2019Aug1
8744441/10/2019
98444510/3/2019
109444610/5/2019
111044465/10/2019
121144465/10/2019
131244465/10/2019
1413444710/10/2019
1514444710/10/2019
1615444810/15/2019
1716444910/20/2019
1817445010/30/2019
1918445111/2/2019
2019445211/4/2019
2120445311/8/2019
222144538/11/2019
2322445411/9/2019
2423445611/10/2019
Sheet1
 
Upvote 0
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>
 
Upvote 0
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,
 
Upvote 0
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,
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top