COUNT DUPLICATES IN MULTIPLE CRITERIA

[msalas0308]

Hello. I am trying to find the correct formula to count duplicate entries with multiple criteria's. See example below.

I need a formula to count all Orders in a spreadsheet that has certain words in that column as well is certain words in another column.

I need to find out how many Orders I have in Column "D" (counting duplicate Orders as 1), that have a Plant Section of MPC, SRU, CK1, and CK2 in Column "B" with a Priority of 2 in Column "C". My answer would be 4 if I had the correct formula.

2020 Sched Compliance Metrics.xlsx
	B	C	D
1	Plant Section	Priority	Order
4	SRU	2	60981719
5	SRU	2	60981719
6	SRU	2	60981719
7	CK2	2	61594103
8	CK2	2	61594103
9	CK2	2	61594103
11	MPC	2	61627849
12	EBK	F	61826948
13	EBK	F	61826948
14	WBK	2	61855688
15	CK1	1	61911814
16	MPC	2	61911823
17	MPC	2	61911823
18	MPC	2	61911823
19	MPC	2	61911823
21	WWT	F	61924403
22	WWT	F	61924403
23	CK2	1	61983723
24	WBK	2	61990374
25	EBK	1	62017883
27	WBK	2	62031194
28	EBK	2	62040503
WK 33

 

[msalas0308]

Thanks for that. Strange message though as you would think that then the FILTER function should return #NAME? & therefore simply be ignored by by the COUNT function. I had tried putting an obviously incorrect function name in place of FILTER and my formula simply returned 0.

In any case, if FILTER is not available then neither will UNIQUE, so perhaps this might do the job?

msalas0308.xlsm
	B	C	D	E	F
1	Plant Section	Priority	Order
2
3					Count
4	SRU	2	60981719		4
5	SRU	2	60981719
6	SRU	2	60981719
7	CK2	2	61594103
8	CK2	2	61594103
9	CK2	2	61594103
10
11	MPC	2	61627849
12	EBK	F	61826948
13	EBK	F	61826948
14	WBK	2	61855688
15	CK1	1	61911814
16	MPC	2	61911823
17	MPC	2	61911823
18	MPC	2	61911823
19	MPC	2	61911823
20
21	WWT	F	61924403
22	WWT	F	61924403
23	CK2	1	61983723
24	WBK	2	61990374
25	EBK	1	62017883
26
27	WBK	2	62031194
28	EBK	2	62040503
Sheet1

Cell Formulas
Range	Formula
F4	F4	=SUM(N(FREQUENCY(IF(ISNUMBER(MATCH(B4:B28,{"MPC","SRU","CK1","CK2"},0)),IF(C4:C28=2,D4:D28)),D4:D28)>0))

It came back as #VALUE!

 

[Peter_SSs]

It came back as #VALUE!

With the identical sample data?

 

[msalas0308]

I think I figured it out.