formula help - if(a=b,count(c))

Microsoft Office excel 2007 home & stude...
April 18, 2011 at 09:07:05
Specs: Windows XP
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!


See More: formula help - if(a=b,count(c))

Report •


#1
April 18, 2011 at 10:16:01
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

http://www.skeptic.com/


Report •
Related Solutions


Ask Question