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

 

[Richynero]

Ok, so what you need to do is add an extra column that has a unique item based on your 3 inputs by concatenate your data. For instance in E4 you want to type = B4&"-"&C4"-"&D4. This will give you a key from which to work on. You then need to a countif in column F. If you type in F4 the following = COUNIF($E$4:$E$28,E4). Copy that formula down and hey presto! This will tell you which are duplicates.

Hope that helps!

 

[kweaver]

I created a help using UNIQUE. Does this do it?

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

Cell Formulas
Range	Formula
E2:G14	E2	=UNIQUE(A2:C23)
I2	I2	=SUMPRODUCT(((E:E="SRU")+(E:E="MPC")+(E:E="CK1")+(E:E="CK2"))*(F:F=2))
Dynamic array formulas.

 

[AhoyNC]

Since you have Excel 365 here is an option without helper columns.

Book1
	A	B	C	D	E
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
Sheet4

Cell Formulas
Range	Formula
E4	E4	=ROWS(UNIQUE(FILTER($C$4:$C$28,($B$4:$B$28=2)*(($A$4:$A$28="MPC")+($A$4:$A$28="SRU")+($A$4:$A$28="CK1")+($A$4:$A$28="CK2")))))

 

[Peter_SSs]

Another option that is slightly more compact and also will return a count of zero rather than an error if nothing meets the criteria.

20 10 06.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
count

Cell Formulas
Range	Formula
F4	F4	=COUNT(UNIQUE(FILTER(D4:D28,(C4:C28=2)*(ISNUMBER(FIND("|"&B4:B28&"|","|MPC|SRU|CK1|CK2|"))))))

 

[msalas0308]

Peter_SSs - It says "That function isn't valid".

 

[Peter_SSs]

Peter_SSs - It says "That function isn't valid".

Does it give any indication about what part of the formula is the problem?

What happens if you click this icon ...

... at the top of my screen shot and then select cell B1 on a blank sheet and Paste?

Do you have the FILTER and UNIQUE functions in your version of Excel 365?

 

[Fluff]

Does it give any indication about what part of the formula is the problem?

I've seen people getting that message when trying to use the Filter function, if they don't have it. So suspect that the OP does not have the latest updates.

 

[msalas0308]

Peter_SSs, Fluff - it's the Filter.

 

[Peter_SSs]

I've seen people getting that message when trying to use the Filter function, if they don't have it.

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))