Find Duplicate Values using a Criteria

[GreyFox8991]

Hello Excel Community,

Wondering if someone might have some insight into the following. I am trying to find duplicate values based on a criteria which i will explain below. I am attempting to use the countifs formula to review but i keep getting an error and I cannot figure out what might be the exact issue. I have a Hierarchy column which shows the following, with "1" being considered the "TRUE" Request value:

1 = Complete
2 = Open
3 = Cancel

What I am trying to accomplish is a formula showing the 1 hierarchy value as the "True Request" while pinpointing the hierarchy values 2 and 3 as duplicates. Does anyone have a better way to show this possibly? Any insight would be greatly appreciated!

The formula I am attempting is =COUNTIFS(B3:B5|B3|$D$2|D3)

Book14
	A	B	C	D	E
1	ID	OrderID	Status	Hierarchy	Duplicate?
2	9999999	123456	Cancel	3	#VALUE!
3	9999999	78910112	Complete	1	#VALUE!
4	9999999	13141516	Open	2	#VALUE!
Sheet3

Cell Formulas
Range	Formula
E2:E4	E2	=COUNTIFS(B2:B4,B2,$D$2,D2)

 

[hagia_sofia]

Hello, would this work for you:

=IF(D2=AGGREGATE(15,6,($D$2:$D$4)/($A$2:$A$4=A2),1),"True Request","Duplicate")

 

[Eric W]

I'm confused. Wouldn't this work:

Book1
	A	B	C	D	E
1	ID	OrderID	Status	Hierarchy	Duplicate?
2	9999999	123456	Cancel	3	Dulplicate
3	9999999	78910112	Complete	1	True Request
4	9999999	13141516	Open	2	Dulplicate
5	1234567	333	Open	2	Dulplicate
6	1234567	444	Cancel	3	Dulplicate
7	1234567	555	Complete	1	True Request
Sheet1

Cell Formulas
Range	Formula
E2:E7	E2	=IF(D2=1,"True Request","Dulplicate")