Count instances in one column based on data in another column

[pacerfan07]

Based on the table below, I would like to create another table that looks at the names in the left column and counts how many instances the number is greater than 5 in the right column. So the new table would say John Smith's count is 2; Kevin Nance's count is 1, etc. All help is appreciated. Thanks!

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Kevin Nance[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Sharon Burros[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Stanley Kirk[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Sharon Burros[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]Stanley Kirk[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Kevin Nance[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]

 

[Manuel_Spreadsheet]

Assuming your sample data is in columns A:B, try the following formula next to it in column C:

=COUNTIFS($A$2:$A$10,A2,$B$2:$B$10,">5")

 

[MARZIOTULLIO]

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

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]John Smith[/TD]
[TD]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]John Smith[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Kevin Nance[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]Kevin Nance[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Sharon Burros[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]Sharon Burros[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Stanley Kirk[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]Stanley Kirk[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]John Smith[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]Sharon Burros[/TD]
[TD]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]John Smith[/TD]
[TD]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]Stanley Kirk[/TD]
[TD]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]Kevin Nance[/TD]
[TD]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

F1=SUMPRODUCT(($A$1:$A$9=$E1)*($B$1:$B$9>5)) copy down

 

[pacerfan07]

Thanks, both of these suggestions worked.