Microsoft Office excel 2007 home & stude...

Let me clarify my title, i have a massive spreadsheet someone else created, it's a bit of a mess, it won't let me create a pivot table the way it was created, and I can't mess with it. so I'm left trying to figure out a formula to do what i need. The data is in a tab called "2010 - 2011 CSG PD". I am creating a summary on a tab called "Stats per School". on the Stats per School tab i have a list of each school listed in column A, in column B I need a formula that will look through all of column "L" in 2010 - 2011 CSG PD and for each occurrence of a specific school name I need it to count the number of students...BUT...

Social Insurance Numbers (column A of 2010 - 2011 CSG PD) identify different students, but most students are in multiple times for different reasons, in Column G they have M for male and F for female only once per SIN. So i need the formula to find each time "UNB Fredericton" occurs in column L and count the number of "M" and "F" in column G.

So if UNB Fredericton shows up 41 times in the spreadsheet, but only 25 of them have M or F in column G i want 25 to show up in column B of Stats per School.

I tried this:

=COUNTIF('2010 - 2011 CSG PD '!$L$9:$L$482,"UNB Fredericton")

but it gives me 41I tried this:

=COUNT(IF('2010 - 2011 CSG PD '!$L$9:$L$482=A5,'2010 - 2011 CSG PD '!$G$9:$G$482,0))

but it gives me 0i tried this:

=IF('2010 - 2011 CSG PD '!$L$9:$L$482=A5,COUNT('2010 - 2011 CSG PD '!$G$9:$G$482))

but it gives me an error saying value is the wrong data typeand i tried this:

=IF('2010 - 2011 CSG PD '!$L$9:$L$482=A5,SUMPRODUCT('2010 - 2011 CSG PD '!$G$9:$G$482),0)

but it also gave me an error saying value is the wrong data typeI'm at a loss, I've read through every single COUNT and COUNTIF tread here, i tried everything and can't figure this out.

It seems so simple, what am i doing wrong?? Thank you all for any help!

Just as a quick response, have you tried =COUNTIFS() (NOTE, the S at the end)

It's a new function in 2007

It may help with your problem.MIKE

Ask Your Question

Weekly Poll

Do you agree with Google's decision to shut down Google+?

Discuss in The Lounge

Poll History