Count Numbers without counting the duplicates

[Kathy Garcia]

Need help please. I have list of issued PO. How to count number of PO issued without counting the duplicate numbers. Numerous items are generated if more than items indicated in the PO. I want to determine how many PO issued per department. What is the formula?

 

[awoohaw]

i can only think of it with a helper column in 2013:
I'm sure there is a single formula method, but this my best attempt:

Book1
	A	B	C
2	PO Nbr	1st Instance	Count
3	171	1	6
4	172	1
5	172	0
6	172	0
7	172	0
8	172	0
9	172	0
10	174	1
11	174	0
12	178	1
13	181	1
14	182	1
15	182	0
16	182	0
17	182	0
Sheet1

Cell Formulas
Range	Formula
C3	C3	=SUM(B3:B17)
B3:B17	B3	=IF(COUNTIF($A$3:A3,A3)=1,1,0)

 

[kevin9999]

You may need to enter this with Ctrl+Shift+Enter.

Book1
	A	B
1	PO Nbr	Count
2	171	6
3	172
4	172
5	172
6	172
7	172
8	172
9	174
10	174
11	178
12	181
13	182
14	182
15	182
16	182
Sheet1

Cell Formulas
Range	Formula
B2	B2	=SUM(1/COUNTIF(A2:A16,A2:A16))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[awoohaw]

You may need to enter this with Ctrl+Shift+Enter.

Book1
	A	B
1	PO Nbr	Count
2	171	6
3	172
4	172
5	172
6	172
7	172
8	172
9	174
10	174
11	178
12	181
13	182
14	182
15	182
16	182
Sheet1

Cell Formulas
Range	Formula
B2	B2	=SUM(1/COUNTIF(A2:A16,A2:A16))
Press CTRL+SHIFT+ENTER to enter array formulas.

@kevin9999 That is a very elegant solution. Very sharp!

 

[Kathy Garcia]

Thank you Awoohaw! It works.

 

[kevin9999]

@kevin9999 That is a very elegant solution. Very sharp!

Thank you

 

[Kathy Garcia]

Thank you Awoohaw! It works. The formula in column B is what I needed since summary can be generated from pivot table

 

[awoohaw]

my pleasure. Best wishes!