COUNTIF and VLOOKUP in one formula

Hi everyone!

I have 2 worksheets

Worksheet1 contains employee names and date, time, and the type of error they committed. My Worksheet2 pretty much looks like this:

Error 1 Error 2 Error 3
Employee 1
Employee 2
Employee 3

Basically I want excel to look for Employee 1 in worksheet 1 and count how many times that employee committed Error 1, Error 2, Error 3 and so on and give me that number in my worksheet2.

I've been trying a formula with Vlookup and Countif but it doesn't work. I really appreciate it if somebody can point me to the right direction... or formula!

Cheers!

I notice you are using Excel 2010. You can use COUNTIFS for this purpose.

Formula:

Please Login or Register  to view this content.

Thank you but I tried it and this doesn't work for me

This is the formula I've been trying

=COUNTIF(VLOOKUP(E3,'Worksheet1'!E561:R1965,5,FALSE)="Error 1")

If I am to translate what I want excel to do in words, I want it to look up for the name of employee (E3) in Worksheet1, check in column 5 of that worksheet (where the errors are listed) and COUNT the number of time the employee's name (E3) appeared ONLY IF it is equal to Error 1 and put that number in my worksheet 2

Example: in Worksheet 2 the name ANNA BANANA is in cell E3, now ANNA BANANA committed 30 errors in a month; Several Error 1, a few Error 2 and some Error 3

I wanted to know exactly how many Error 1 she committed, but if I use a countif formula alone what excel does is it counts all occurrences of Error 1 in worksheet 1 so I was trying to incorporate a vlookup in the formula and I'm not getting anywhere.

I am talking about COUNTIFS not COUNTIF. So deducing from your formula, the formula should be...

Formula:

Please Login or Register  to view this content.

Got it! Thank you!