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 trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History