Combining CountIF and match or vlookup

C-Webz Source

so lets get into it. I have 2 sheets, (Sheet1, Sheet2)

In Sheet1 there is a column (B) that contains players names, and another column (C) that contains a number (15).

In Sheet2 there is a list of names in Column A and then columns B:Q have numbers in them relating to the player name in Column A.

I need to find the player name (from cell B in sheet1) in Sheet2 and then count how many numbers in the row from B:Q that correspond with that player are >= C(from Sheet1)

Can anyone help? I am trying to figure out if there is a way using index and match or vlookup but striking out. I have a whole table of names in sheet1 I have to do this for. Any help is appreciated.

excelexcel-formulaexcel-2010

Answers

answered 3 years ago Balinti #1

Open a new column R on the 2nd sheet and insert to the 2nd Row:

=COUNTIF(b2:q2,">="&VLOOKUP(a2,Sheet1!B:C,2,0))

It finds the match on the 1st Sheet and the count how many values are equal or bigger than it.

You can drag it to the bottom.

comments powered by Disqus