How do i combine a vlookup function with a countif function?

My dilema is that everyone is not always in the same postion on the roster each day, so that I require the formula to look down column C to find that
person and then to look across the row to count up the gates he/she has.

I have got sheet3 Gate matrix column B set up to read the names from day 1, and they are not always in the same positions for the remaining 3 days.

Column G - I have a simple autosum set up her to count the total number of gates for an individual over the 4 days. =SUM(C5:F5)

Column H - I want the matrix to count up how many last gates people have over the 4 day work roster, using the roster on sheet 2.

Column I - I want the matrix to count up how many 1st gates people have over the 4 day work roster, using the roster on sheet 2.

Column J - I want the matrix to count how many days people have done on Wgate and not individual W Gates or W Gate Off, using the roster on sheet 2.

Column K - I want this to only work off the very last day and indicate which 2 people are doing the last 2 gates (ENT Gate & EX Gate), using the roster on sheet 2.

In column C and cell C3 I was trying the following formula to see if would find the correct person and then count the number of gates that the person had for that day. The formula that I was trying to use was:
=AND(VLOOKUP(B7,SHEET2!C3:K15,1,FALSE),COUNTIF(SHEET2!D5:K5,"*?GATE"))

The return value that I received for this was #VALUE

Okay, I'm just going to explain how to do one instead of doing the entire sheet for you, since I'm not sure on what you were asking for anyway...

=COUNTIF(INDIRECT("Sheet2!D"&MATCH($B5,Sheet2!$C$3:$C$15,0)+2&":K"&MATCH($B5,Sheet2!$C$3:$C$15,0)+2),"*"&"GATE"&"*")

In cell C5 on sheet 3, then double click it down.

Now to explain this function...

I am utilizing indirect to give the range that is described in text within. We are utilizing the match function to find the position of the match in the given range, then applying a correction number so it spits out the row value. Utilizing that trick, you can have it look at all of the correct ranges in your other page, regardless of how you organize the names, etc.

So for example, in the day two column, it would change to this:

=COUNTIF(INDIRECT("Sheet2!D"&MATCH($B5,Sheet2!$C$17:$C$29,0)+16&":K"&MATCH($B5,Sheet2!$C$17:$C$29,0)+16),"*"&"GATE"&"*")

I locked the B column by entering it as $B5, so you can simply drag the formula to the right and adjust it as necessary. If you have a lot of different days, you could make it so every-time you drag it right it auto-does the corrections for you (making it a super complicated formula), but your sample data doesn't suggest this is necessary.

It would probably be smarter to re-format how your data is than attempting to create a massive formula if manually redo-ing this is too much for you. For example, creating named ranges for each day, then utilizing indirect and the columns function to return everything in that named range. If you called them Day 1, etc.

=indirect("Day "&columns($A:A))

Would refer to the given range (and drag right, changing to Day 1, Day 2, etc. instead of having all of the correction factors etc. It would just be a matter of setting up all the named ranges then you could refer back to them easily.

Hope this helps.