Count Unique values from a column based on criteria

[igorteo]

Hello everyone,

I'm facing a problem of counting unique values in one column, based on multiple criteria. I want to count the unique Part Numbers from column "C" in "M3" cell if the "K2" cell is equal to some of the records in column "A"
I have tried SUMPRODUCT, COUNTIFS, FREQUENCY, but I'm doing something.

A​

B

C

D

E​

F

G

H

I

J

K

L

M

N

O

TRACKING #

LOCATION NAME

PART NUMBER

NOMENCLATURE

UNIT PRICE

QTY

UNIT OF ISSUE

COST POST APPROVED

DATE SHIPPED

TRACKING #

LOCATION NAME

LINE ITEMS

TOTAL PRICE

DATE SHIPPED

TK-44582

LONDON

650-985

BOLT,MACHINE

$ 3.76

4

EA

$15.04

19-Mar-20

TK-44582

LONDON

7

$1,912.39

19-Mar-20

TK-44582

LONDON

900-123

STRAP,TIE DOWN,ELECTR

$ 2.46

7

HD

$17.22

19-Mar-20

TK-44583

LONDON

8

$721.67

19-Mar-20

TK-44582

LONDON

850-658

HOSE,PREFORMED

$ 21.73

3

EA

$65.19

19-Mar-20

TK-44582

LONDON

700-325

HARDWARE KIT,ELECTRON

$ 7.89

7

EA

$55.23

19-Mar-20

TK-44582

LONDON

850-658

HINGE,DOOR,VEHICULAR

$ 108.69

3

EA

$326.07

19-Mar-20

TK-44582

LONDON

650-985

SPACER,SLEEVE

$ 37.84

7

PG

$264.88

19-Mar-20

TK-44582

LONDON

700-325

ARM,STEERING GEAR

$ 154.25

7

EA

$1,079.75

19-Mar-20

TK-44582

LONDON

900-123

GAGE ROD,LIQUID LEVEL

$ 9.89

3

EA

$29.67

19-Mar-20

TK-44582

LONDON

100-552

GAGE ROD,LIQUID LEVEL

$ 9.89

3

EA

$29.67

19-Mar-20

TK-44582

LONDON

400-567

GAGE ROD,LIQUID LEVEL

$ 9.89

3

EA

$29.67

19-Mar-20

TK-44583

LONDON

UM4639040C

INSULATOR ENGINE

$ 13.07

5

EA

$65.35

19-Mar-20

TK-44583

LONDON

UD2D-51-150-F

LAMP RR RH 2010

$ 32.26

6

EA

$193.56

19-Mar-20

TK-44583

LONDON

UH74 32280

END ASSY TIE ROD OUTER

$ 14.30

8

EA

$114.40

19-Mar-20

TK-44583

LONDON

UH74-34-155

BRACKET STABILIZER BUSH FRONT

$ 4.90

2

EA

$9.80

19-Mar-20

TK-44583

LONDON

UH74-34-156

BUSH STABILIZER BAR FROMT

$ 2.57

3

EA

$7.71

19-Mar-20

TK-44583

LONDON

WL8113ZA5TA

FILTER FUEL

$ 10.76

1

EA

$10.76

19-Mar-20

TK-44583

LONDON

UH74 32280

END ASSY TIE ROD OUTER

$ 14.30

6

EA

$85.80

19-Mar-20

TK-44583

LONDON

SA67-25-06XA

JOINT UNIVERSAL AXLE DRIVE

$ 62.69

1

EA

$62.69

19-Mar-20

TK-44583

LONDON

1235478

END ASSY TIE ROD OUTER

$ 14.30

12

EA

$171.60

19-Mar-20

 

[Peter_SSs]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.

 

[kennypete]

These formulas should do it (noting @Peter_SSs's caveat on your Excel version) if you have a relatively recent version:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	TRACKING #	LOCATION NAME	PART NUMBER	NOMENCLATURE	UNIT PRICE	QTY	UNIT OF ISSUE	COST POST APPROVED	DATE SHIPPED		TRACKING #	LOCATION NAME	LINE ITEMS	TOTAL PRICE	DATE SHIPPED
2	TK-44582	LONDON	650-985	BOLT,MACHINE	$3.76	4	EA	$15.04	19/03/2020		TK-44582	LONDON	10	$1,912.39	19/03/2020
3	TK-44582	LONDON	900-123	STRAP,TIE DOWN,ELECTR	$2.46	7	HD	$17.22	19/03/2020		TK-44583	LONDON	9	$721.67	19/03/2020
4	TK-44582	LONDON	850-658	HOSE,PREFORMED	$21.73	3	EA	$65.19	19/03/2020
5	TK-44582	LONDON	700-325	HARDWARE KIT,ELECTRON	$7.89	7	EA	$55.23	19/03/2020
6	TK-44582	LONDON	850-658	HINGE,DOOR,VEHICULAR	$108.69	3	EA	$326.07	19/03/2020
7	TK-44582	LONDON	650-985	SPACER,SLEEVE	$37.84	7	PG	$264.88	19/03/2020
8	TK-44582	LONDON	700-325	ARM,STEERING GEAR	$154.25	7	EA	$1,079.75	19/03/2020
9	TK-44582	LONDON	900-123	GAGE ROD,LIQUID LEVEL	$9.89	3	EA	$29.67	19/03/2020
10	TK-44582	LONDON	100-552	GAGE ROD,LIQUID LEVEL	$9.89	3	EA	$29.67	19/03/2020
11	TK-44582	LONDON	400-567	GAGE ROD,LIQUID LEVEL	$9.89	3	EA	$29.67	19/03/2020
12	TK-44583	LONDON	UM4639040C	INSULATOR ENGINE	$13.07	5	EA	$65.35	19/03/2020
13	TK-44583	LONDON	UD2D-51-150-F	LAMP RR RH 2010	$32.26	6	EA	$193.56	19/03/2020
14	TK-44583	LONDON	UH74 32280	END ASSY TIE ROD OUTER	$14.30	8	EA	$114.40	19/03/2020
15	TK-44583	LONDON	UH74-34-155	BRACKET STABILIZER BUSH FRONT	$4.90	2	EA	$9.80	19/03/2020
16	TK-44583	LONDON	UH74-34-156	BUSH STABILIZER BAR FROMT	$2.57	3	EA	$7.71	19/03/2020
17	TK-44583	LONDON	WL8113ZA5TA	FILTER FUEL	$10.76	1	EA	$10.76	19/03/2020
18	TK-44583	LONDON	UH74 32280	END ASSY TIE ROD OUTER	$14.30	6	EA	$85.80	19/03/2020
19	TK-44583	LONDON	SA67-25-06XA	JOINT UNIVERSAL AXLE DRIVE	$62.69	1	EA	$62.69	19/03/2020
20	TK-44583	LONDON	1235478	END ASSY TIE ROD OUTER	$14.30	12	EA	$171.60	19/03/2020
Sheet1

Cell Formulas
Range	Formula
M2:M3	M2	=COUNTIFS(A:A,K2,I:I,O2)
N2:N3	N2	=SUMIFS(H:H,A:A,K2,I:I,O2)

 

[Peter_SSs]

These formulas should do it

I think you may have missed ..

I want to count the unique Part Numbers ...

Here are two suggestions.
Column M requires Excel 365 with the new dynamic array functions.
Column N for older versions.
(I've highlighted cells for my own benefit to show which part numbers should be counted)

20 03 19.xlsm
	A	B	C							J	K	L	M	N
1	TRACKING #	LOCATION NAME	PART NUMBER								TRACKING #	LOCATION NAME	LINE ITEMS	LINE ITEMS
2	TK-44582	LONDON	650-985								TK-44582	LONDON	6	6
3	TK-44582	LONDON	900-123								TK-44583	LONDON	8	8
4	TK-44582	LONDON	850-658
5	TK-44582	LONDON	700-325
6	TK-44582	LONDON	850-658
7	TK-44582	LONDON	650-985
8	TK-44582	LONDON	700-325
9	TK-44582	LONDON	900-123
10	TK-44582	LONDON	100-552
11	TK-44582	LONDON	400-567
12	TK-44583	LONDON	UM4639040C
13	TK-44583	LONDON	UD2D-51-150-F
14	TK-44583	LONDON	UH74 32280
15	TK-44583	LONDON	UH74-34-155
16	TK-44583	LONDON	UH74-34-156
17	TK-44583	LONDON	WL8113ZA5TA
18	TK-44583	LONDON	UH74 32280
19	TK-44583	LONDON	SA67-25-06XA
20	TK-44583	LONDON	1235478
21
Count Unique

Cell Formulas
Range	Formula
M2:M3	M2	=COUNTA(UNIQUE(FILTER(C$2:C$20,A$2:A$20=K2)))
N2:N3	N2	=SUMPRODUCT((C$2:C$20<>"")*(A$2:A$20=K2)/COUNTIF(C$2:C$20,C$2:C$20&""))

 

[igorteo]

Thank you so so much. "=SUMPRODUCT((C$2:C$20<>"")*(A$2:A$20=K2)/COUNTIF(C$2:C$20,C$2:C$20&""))" works perfect.

You are the best.

Thanks once again

 

[Peter_SSs]

You're welcome.
Don't forget this so people don't waste time giving you solutions that don't work in your version of Excel.

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.

 

[kennypete]

I think you may have missed ..

Yes, I misread the "K3" cell point - I thought the question was how to make M2:N3 into formulas.
The challenges of getting tabular data in a post instead of XL2BB....