# 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 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!

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

#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 2007It may help with your problem.MIKEhttp://www.skeptic.com/

Report •
Related Solutions