Countif + vlookup of multiple values

[seack]

Hi all,

First time poster, long time viewer. I've been looking through the forums trying to find someone with a similar problem as mine but haven't quite nailed it on the head. I am working with an Excel 2010 workbook that has two worksheets in it. What I am trying to accomplish is I want the second worksheet to scan the first worksheet for a student's name, and count all of the instances that the student has a score less than a certain threshold (we'll say "5" for this example). I have tried using various combinations of vlookup and countif functions, but have not had much success. I did get it to a point where it worked, but only for the first instance of that student's name; it wouldn't continue searching the first worksheet for any other instances.

I have attached a sample workbook as a reference. Any help is much appreciated; thank you.

[Pete_UK]

Did you forget to attach the workbook? I can't see one in your post.

Pete

[seack]

Sorry, it's attached now.

[FDibbins]

OK wait, I just got a "report post" from you asking "Please delete this post, wrong attachment included"
??

[seack]

Yes,

Originally I included some sensitive data by accident and wasn't sure how to remove it. I have since uploaded the correct attachment (that's why you couldn't find it originally). Sorry.

[Pete_UK]

Put this in B2 of the Performance sheet:

=COUNTIFS('Student Data'!$A:$A,$A2,'Student Data'!$B:$B,"<5")

and this one in C2:

=COUNTIFS('Student Data'!$A:$A,$A2,'Student Data'!$B:$B,">=5")

(NOTE that I have made it >=5, rather than >5). Then copy both formulae down.

Hope this helps.

Pete

[seack]

Works a treat Pete, thank you. I must have been making it much more complicated than I need to. Would you mind explaining what exactly that formula is doing so I can "understand" the logic behind the equation?

[Pete_UK]

The COUNTIFS function is used for multiple-condition counts (COUNTIF can only be used for a single condition). In your case you have two conditions - you are interested in a particular name (in column A of the Performance sheet) AND where the score is below 5 (for column B). The syntax for COUNTIFS is that you have pairs of terms, the first relating to the range and the second relating to the condition, so to paraphrase the 1st formula that I gave you, it is saying:

COUNT the number of entries IF the names on the Data sheet = A2 AND IF the scores on the Data sheet is less than 5

Hope this helps.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[FDibbins]

OK confusion reigned and we all got wet lol. As long as we are were we need to be now