Need help with countif/vlookup or countif/index/match

[kchung6541]

I am trying to find a formulaic way to count individuals by role, who have attainment within a range. I've made a sample data set below. I need a formula to populate the values in the yellow cells. Obviously my data set is 1000's of rows, so I would prefer not to filter and count. Is there a way to do this with a vlookup or index/match along with count? I tried the formula in the screenshot, but it doesn't work. THANKS!!
Excel 2010 64 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH][/TH]
[TH]

A​

[/TH]
[TH]

B​

[/TH]
[TH]

C​

[/TH]
[TH]

D​

[/TH]
[TH]

E​

[/TH]
[TH]

F​

[/TH]
[TH]

G​

[/TH]
[TH]

H​

[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

1​

[/TD]
[TD]Name[/TD]
[TD]Role[/TD]
[TD]Attainment[/TD]
[TD][/TD]
[TD]Role[/TD]
[TD]<50%[/TD]
[TD]50 - 75%[/TD]
[TD]>75%[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

2​

[/TD]
[TD]Bob[/TD]
[TD]Rep[/TD]
[TD]

50%​

[/TD]
[TD][/TD]
[TD]Rep[/TD]
[TD="bgcolor: #FFFF00"]

1​

[/TD]
[TD="bgcolor: #FFFF00"]

3​

[/TD]
[TD="bgcolor: #FFFF00"]

2​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

3​

[/TD]
[TD]Joe[/TD]
[TD]Engineer[/TD]
[TD]

50%​

[/TD]
[TD][/TD]
[TD]Engineer[/TD]
[TD="bgcolor: #FFFF00"]

1​

[/TD]
[TD="bgcolor: #FFFF00"]

3​

[/TD]
[TD="bgcolor: #FFFF00"]

2​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

4​

[/TD]
[TD]Mary[/TD]
[TD]Rep[/TD]
[TD]

95%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

5​

[/TD]
[TD]Tom[/TD]
[TD]Engineer[/TD]
[TD]

95%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

6​

[/TD]
[TD]Sue[/TD]
[TD]Rep[/TD]
[TD]

30%​

[/TD]
[TD][/TD]
[TD]=COUNTIF(B:B,VLOOKUP(E2,B:C,2,FALSE)>".5")[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

7​

[/TD]
[TD]Jack[/TD]
[TD]Engineer[/TD]
[TD]

30%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

8​

[/TD]
[TD]Simon[/TD]
[TD]Rep[/TD]
[TD]

65%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

9​

[/TD]
[TD]Jessica[/TD]
[TD]Engineer[/TD]
[TD]

65%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

10​

[/TD]
[TD]Lynn[/TD]
[TD]Rep[/TD]
[TD]

120%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

11​

[/TD]
[TD]Sarah[/TD]
[TD]Engineer[/TD]
[TD]

120%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

12​

[/TD]
[TD]Brad[/TD]
[TD]Rep[/TD]
[TD]

60%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

13​

[/TD]
[TD]Yvonne[/TD]
[TD]Engineer[/TD]
[TD]

60%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet2[/TD]
[/TR]
</tbody>[/TABLE]

 

[JoeMo]

Copy F2:H2 down.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Name	Role	Attainment	*	Role		50 - 75%	>75%
2	Bob	Rep	50%	*	Rep	1	3	2
3	Joe	Engineer	50%	*	Engineer	1	3	2
4	Mary	Rep	95%	*	*	*	*	*
5	Tom	Engineer	95%	*	*	*	*	*
6	Sue	Rep	30%	*	*	*	*	*
7	Jack	Engineer	30%	*	*	*	*	*
8	Simon	Rep	65%	*	*	*	*	*
9	Jessica	Engineer	65%	*	*	*	*	*
10	Lynn	Rep	120%	*	*	*	*	*
11	Sarah	Engineer	120%	*	*	*	*	*
12	Brad	Rep	60%	*	*	*	*	*
13	Yvonne	Engineer	60%	*	*	*	*	*
Sheet5

 

[kchung6541]

Thanks Joe. That's a great solution. I was trying to make it too complicated.

 

[JoeMo]

Thanks Joe. That's a great solution. I was trying to make it too complicated.

You are welcome - thanks for the reply.