Count unique based on date creation

[reza_doang]

Hi,

I want to count the unique PO based on date created

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]PO #[/TD]
[TD]PO Created[/TD]
[/TR]
[TR]
[TD]1200027217[/TD]
[TD]3/13/2019[/TD]
[/TR]
[TR]
[TD]1200027217[/TD]
[TD]3/13/2019[/TD]
[/TR]
[TR]
[TD]1200027218[/TD]
[TD]3/14/2019[/TD]
[/TR]
[TR]
[TD]1200027219[/TD]
[TD]3/13/2019[/TD]
[/TR]
[TR]
[TD]1200027219[/TD]
[TD]3/13/2019[/TD]
[/TR]
[TR]
[TD]1200027219[/TD]
[TD]3/13/2019[/TD]
[/TR]
[TR]
[TD]1200027211[/TD]
[TD]3/10/2019[/TD]
[/TR]
[TR]
[TD]1200027211[/TD]
[TD]3/10/2019[/TD]
[/TR]
[TR]
[TD]1200027209[/TD]
[TD]3/13/2019[/TD]
[/TR]
[TR]
[TD]1200027204[/TD]
[TD]3/14/2019[/TD]
[/TR]
[TR]
[TD]1200027201[/TD]
[TD]3/14/2019[/TD]
[/TR]
[TR]
[TD]1200027231[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1200027233[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

condition
1 PO will have same creation date

from above sample, the answer should be
3/10/2019 = 1
3/13/2019 = 3
3/14/2019 = 3
blank = 2

really appreciate with the help

Thanks

 

[sandy666]

is that what you want?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]PO #[/td][td=bgcolor:#5B9BD5]PO Created[/td][td][/td][td=bgcolor:#70AD47]PO Created[/td][td=bgcolor:#70AD47]Count[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

1200027217​

[/td][td=bgcolor:#DDEBF7]

13/03/2019​

[/td][td][/td][td=bgcolor:#E2EFDA]

13/03/2019​

[/td][td=bgcolor:#E2EFDA]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

1200027217​

[/td][td]

13/03/2019​

[/td][td][/td][td]

14/03/2019​

[/td][td]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

1200027218​

[/td][td=bgcolor:#DDEBF7]

14/03/2019​

[/td][td][/td][td=bgcolor:#E2EFDA]

10/03/2019​

[/td][td=bgcolor:#E2EFDA]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

1200027219​

[/td][td]

13/03/2019​

[/td][td][/td][td][/td][td]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

1200027219​

[/td][td=bgcolor:#DDEBF7]

13/03/2019​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

1200027219​

[/td][td]

13/03/2019​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

1200027211​

[/td][td=bgcolor:#DDEBF7]

10/03/2019​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

1200027211​

[/td][td]

10/03/2019​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

1200027209​

[/td][td=bgcolor:#DDEBF7]

13/03/2019​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

1200027204​

[/td][td]

14/03/2019​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

1200027201​

[/td][td=bgcolor:#DDEBF7]

14/03/2019​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

1200027231​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

1200027233​

[/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][/tr]
[/table]

using PowerQuery (Get&Transform)

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table8"]}[Content],
    ChType = Table.TransformColumnTypes(Source,{{"PO #", type text}, {"PO Created", type date}}),
    Group = Table.Group(ChType, {"PO Created"}, {{"Count", each Table.RowCount(Table.Distinct(_)), type number}})
in
    Group[/SIZE]

 

[steve the fish]

Here is one way. Put your table in columns A and B, your dates in D1:D4 then this in E1:

=SUM(--(FREQUENCY(IF($B$2:$B$100=IF(D1="blank","",D1),IF($A$2:$A$100<>"",$A$2:$A$100)),$A$2:$A$100)>0))

Enter CTRL-SHIFT-ENTER.

 

[reza_doang]

Thanks sandy666 for the suggestion, i am really blind with the power query, but when i look it in google, looks powerful, for sure i will learn it.

Thanks Steve the fish, your formula is working well.
Can you please suggest another query, how if i add more 1 criteria

[TABLE="class: cms_table_grid, width: 500, align: center"]
<tbody>[TR]
[TD]PO #[/TD]
[TD]PO Created[/TD]
[TD]Tier[/TD]
[/TR]
[TR]
[TD]1200027217[/TD]
[TD]3/13/2019[/TD]
[TD]DD[/TD]
[/TR]
[TR]
[TD]1200027217[/TD]
[TD]3/13/2019[/TD]
[TD]DD[/TD]
[/TR]
[TR]
[TD]1200027218[/TD]
[TD]3/14/2019[/TD]
[TD]DD[/TD]
[/TR]
[TR]
[TD]1200027219[/TD]
[TD]3/13/2019[/TD]
[TD]ZZ[/TD]
[/TR]
[TR]
[TD]1200027219[/TD]
[TD]3/13/2019[/TD]
[TD]ZZ[/TD]
[/TR]
[TR]
[TD]1200027219[/TD]
[TD]3/13/2019[/TD]
[TD]ZZ[/TD]
[/TR]
[TR]
[TD]1200027211[/TD]
[TD]3/10/2019[/TD]
[TD]ZZ[/TD]
[/TR]
[TR]
[TD]1200027211[/TD]
[TD]3/10/2019[/TD]
[TD]ZZ[/TD]
[/TR]
[TR]
[TD]1200027209[/TD]
[TD]3/13/2019[/TD]
[TD]DD[/TD]
[/TR]
[TR]
[TD]1200027204[/TD]
[TD]3/14/2019[/TD]
[TD]DD[/TD]
[/TR]
[TR]
[TD]1200027201[/TD]
[TD]3/14/2019[/TD]
[TD]ZZ[/TD]
[/TR]
[TR]
[TD]1200027231[/TD]
[TD][/TD]
[TD]ZZ[/TD]
[/TR]
[TR]
[TD]1200027233[/TD]
[TD][/TD]
[TD]ZZ[/TD]
[/TR]
</tbody>[/TABLE]

so the result will be like this

[TABLE="width: 225"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Tier[/TD]
[TD]PO Created[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]DD[/TD]
[TD="align: right"]3/13/2019[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]3/14/2019[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]ZZ[/TD]
[TD="align: right"]3/13/2019[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]3/14/2019[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]3/10/2019[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD](blank)[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

another thanks in advance