Count/SUM IF function issues

[excellicious]

Column A is a list of employees and column B is the employment dates. These dates range from 1/1/07 to present. In my column C, there are some rows that contained a letter "Y" representing that person is on a special project and the rest of the rows are blank if they're not on a special project.

On another tab, is column A is a list of 1-12 an column B with a list of the months (jan, feb, march.etc..). How can I write a formula to calculate the numbers of "Y" (= 1) in column C according the the hiring date for each month?

I'm not sure if I need to use COUNT, SUMIF, SUMPRODUCT. Thank you.

[jasoncw]

I would use SUMPRODUCT. You may have to change some of the criteria, but this should help you out:

Please Login or Register  to view this content.

HTH

Jason

[excellicious]

Thank you Jason for helping.

I know know why the formula calculate the same return for every month. Please view the attached spreadsheet. The formula should have returned
9- January
10- February
11- March
11- April
11- May
12-June

Thank you.
Excellicious

[daddylonglegs]

Not quite sure about your expected results, but the reason you get the wrong results is that you have a parenthesis in the wrong place - change to this formula in C3 copied down

=SUMPRODUCT((Sheet1!$C$2:$C$18="Y")*(MONTH( Sheet1!$B$2:$B$18)=A3))

[excellicious]

Thank you Daddylonglegs- My goal is to have a running total of the number of staff that are on special project since 2007. In another word, if I have one person on special project in January and another person on special project in February the February total should equal two.

Thanks again.