Need to identify specific data from a big list to determine incentive eligibility for a retail.

[Masterrmind]

I have a big retail list with employee positions. Now I have to justify the policy below with an Excel formula.

1. Every employee has a marking based on their position.
2. If any employee gets a marking "3" for his position then the retail will get a $ 0 incentive.


In the attached sample, Retail 1 will get a $ 5000 incentive because no one from this retail gets the marking of "3". Retail 2 will get a $ 0 incentive because one of the employees from this retail gets the marking of "3". I need to get my result at G column.

Now I have a list of 600+ retail with employees list. Which function should I use?

Pls, see the attached file.

Thanks in advance.

 

[kevin9999]

Book1
	A	B	C	D	E	F	G
1		Outlet Name	Position	Marking		Outlet Name	Output
2		Retail 1	Position 1	1		Retail 1	5000
3		Retail 1	Position 2	2		Retail 2	0
4		Retail 1	Position 3	5
5		Retail 1	Position 4	5
6		Retail 1	Position 5	5
7		Retail 1	Position 6	5
8		Retail 1	Position 7	5
9		Retail 1	Position 8	5
10		Retail 1	Position 9	5
11		Retail 1	Position 10	5
12		Retail 2	Position 1	1
13		Retail 2	Position 2	2
14		Retail 2	Position 3	3
15		Retail 2	Position 4	5
16		Retail 2	Position 5	5
17		Retail 2	Position 6	5
18		Retail 2	Position 7	5
19		Retail 2	Position 8	5
20		Retail 2	Position 9	5
21		Retail 2	Position 10	5
Sheet1

Cell Formulas
Range	Formula
G2:G3	G2	=IF(COUNTIFS(B:B,F2,D:D,"3")=0,5000,0)

 

[Masterrmind]

Book1
	A	B	C	D	E	F	G
1		Outlet Name	Position	Marking		Outlet Name	Output
2		Retail 1	Position 1	1		Retail 1	5000
3		Retail 1	Position 2	2		Retail 2	0
4		Retail 1	Position 3	5
5		Retail 1	Position 4	5
6		Retail 1	Position 5	5
7		Retail 1	Position 6	5
8		Retail 1	Position 7	5
9		Retail 1	Position 8	5
10		Retail 1	Position 9	5
11		Retail 1	Position 10	5
12		Retail 2	Position 1	1
13		Retail 2	Position 2	2
14		Retail 2	Position 3	3
15		Retail 2	Position 4	5
16		Retail 2	Position 5	5
17		Retail 2	Position 6	5
18		Retail 2	Position 7	5
19		Retail 2	Position 8	5
20		Retail 2	Position 9	5
21		Retail 2	Position 10	5
Sheet1

Cell Formulas
Range	Formula
G2:G3	G2	=IF(COUNTIFS(B:B,F2,D:D,"3")=0,5000,0)

Works like a charm......Thanks........

 

[kevin9999]

Happy to help & thanks for the feedback