VLookUP or other formula to count and return a value

Hello Excel Peeps - This request would be for Excel 2010.

Thank for looking at my request and I looking forward to spreading some rep.

Looking for a formula that will look at some data and return a count. I assume this would a VLOOK up formula?

Below is a test work book – loaded with data (I am using 2013 data) and it just has one month for now.
Because I can duplicate modify as needed to create the other months once the foundation formula is built.

Here is an outline of what the test workbook looks like:

Each month will have the following info:

C2 = Month and Year
C3 = Fielding Date (Header)
C4 = Date (Start of Fielding)
E4 = Date (End of Fielding)

You will then see two different sections: Team Lead Ont and Team Lead Alb
• Under these sections there is a list of names.
• To the right of these names are 3 different categories… Promoter, Passive, Detractor

There is one sheet that has raw data on it. It will be added to over the course of the year.
There are 3 headers on this sheet - Interview Date, Location, LTR

He is what I am looking to accomplish.

Promoters category on Nov 2013 tab

• Find the name listed in B6 (Nov tab) on the Fielding Info Tab (Column B)
• Using the fielding date range assigned on the Nov 2013 tab in C4 & E4 look at the dates on the Fielding Info tab (Column A) and …
• Count all Extremely Likely (Column C) between the fielding dates.
• Return value to C6

Passives category on Nov 2013 tab

• Find the name listed in B6 (Nov tab) on the Fielding Info Tab (Column B)
• Using the fielding date range assigned on the Nov 2013 tab in C4 & E4 look at the dates on the Fielding Info tab (Column A) and …
• Count all Very Likely (Column C) between the fielding dates.
• Return value to D6

Detractors category on Nov 2013 tab

• Find the name listed in B6 (Nov tab) on the Fielding Info Tab (Column B)
• Using the fielding date range assigned on the Nov 2013 tab in C4 & E4 look at the dates on the Fielding Info tab (Column A) and …
• Count all Somewhat Likely, Not At All Likely, Not Very Likely (Column C)between the fielding dates.
• Return value to E6

I would then copy and paste this formula down the rest of the sheet - rinse and repeat for each month.

Thanks again - Let me know if you need any other details to help with the formula creation.

Use SUMPRODUCT..see attached