EXCEL : Counting number of Cells that Equal 2 Values...please help 1

[SteveAudus]

I am trying without success to count the number cells in column S contain the value 2, only if the same cell on the that row in column W contains a "F".
But my formula just keeps throwing back #VALUE!

=SUM((IF(ISNA($S$2:$S$202),0,IF($S$2:$S$202=2,1,0)))*($W$2:$W$202="F&quot)

You may of guessed I am trying to count the number of girls with a grade mark 2.

Any suggestions,

Steve Audus
Chaucer School

http://www.chaucer.sheffield.sch.uk

http://www.educationinternet.co.uk

 

[ETID]

Use the pivot table tool....it will do what you need.

 

[SteveAudus]

What is a pivot table tool and how do I use it?

http://www.chaucer.sheffield.sch.uk

http://www.educationinternet.co.uk

 

[nberryman]

Click Data, Pivot Table Wizard and follow the wizard through

Post again if you need more help

Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[nberryman]

Another idea

Click in you data and Click Data, Sort.

Sort by Sex and the by grade and click OK

Next Click Data, Subtotals and select the Grade and select the count function in the box below.

That should give you F and a count of each grade and the same for M


Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[AndreasG]

Hello Steve

you can use the sumproduct function

=SUMPRODUCT((S2:S202=2)*(W2:W202="F&quot)

HTH

Andreas
using xl2000 on win2k

 

[xlbo]

Hi Steve - I believe I gave you that formula a little while back

Are you entering with CTRL+SHIFT+ENTER ???? - it's an array formula so will return the #VALUE! error if you only use ENTER to insert the formula Rgds
~Geoff~

 

[SteveAudus]

xlbo ,yep cheers mate you did but I forgot the ctrl+shift+enter
it works now, but i am using AndreasG's suggestion it seems to work fine and is easy to understand.

Thank you all
Steve

http://www.chaucer.sheffield.sch.uk

http://www.educationinternet.co.uk