|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 41
I 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 0
i 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 type
and 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 type
I'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!