Count unique based on date creation

[reza_doang]

Hi,

I want to count the unique PO based on date created

PO #	PO Created
1200027217	3/13/2019
1200027217	3/13/2019
1200027218	3/14/2019
1200027219	3/13/2019
1200027219	3/13/2019
1200027219	3/13/2019
1200027211	3/10/2019
1200027211	3/10/2019
1200027209	3/13/2019
1200027204	3/14/2019
1200027201	3/14/2019
1200027231
1200027233

<tbody>
</tbody>

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?

PO #	PO Created		PO Created	Count
1200027217​	13/03/2019​		13/03/2019​	3​
1200027217​	13/03/2019​		14/03/2019​	3​
1200027218​	14/03/2019​		10/03/2019​	1​
1200027219​	13/03/2019​			2​
1200027219​	13/03/2019​
1200027219​	13/03/2019​
1200027211​	10/03/2019​
1200027211​	10/03/2019​
1200027209​	13/03/2019​
1200027204​	14/03/2019​
1200027201​	14/03/2019​
1200027231​
1200027233​

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

PO #	PO Created	Tier
1200027217	3/13/2019	DD
1200027217	3/13/2019	DD
1200027218	3/14/2019	DD
1200027219	3/13/2019	ZZ
1200027219	3/13/2019	ZZ
1200027219	3/13/2019	ZZ
1200027211	3/10/2019	ZZ
1200027211	3/10/2019	ZZ
1200027209	3/13/2019	DD
1200027204	3/14/2019	DD
1200027201	3/14/2019	ZZ
1200027231		ZZ
1200027233		ZZ

<tbody>
</tbody>

so the result will be like this

Tier	PO Created	Total
DD	3/13/2019	2
	3/14/2019	2
ZZ	3/13/2019	1
	3/14/2019	1
	3/10/2019	1
	(blank)	2

<colgroup><col><col><col></colgroup><tbody>
</tbody>

another thanks in advance