Excel, Count Cells if certain things match. 1

[SteveAudus]

I have a large selection of grades for our pupils,

I'd like to count the number of certain grades.
As an example

I'd like to count up how many girls have got grade A
or how many boys have got grade B.

I have a column with the grade and a column with gender.

I guess this is easy, but I don't how to do it.

Any Suggestions

Thanks
Steve Audus
Chaucer School, UK

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

http://www.educationinternet.co.uk

 

[xlbo]

If grade in Col B and gender in col C
=SUM((B2:B100="A&quot*(C2:C100="F&quot)
use Ctrl+Shift+Enter to enter the formula rather than just enter - this creates an array formula
Assumes that your gender marker is M & F for boys and girls and grades are represented by a single letter
HTH
Geoff

 

[SteveAudus]

Thanks for your help but...

I should of wrote the grades are a number 1-7,

how do I alter your your formula to count the number grades

Thanks Again
Steve Audus

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

http://www.educationinternet.co.uk

 

[xlbo]

Hi Steve - just substitute your number for "A"
=SUM((B2:B100=1)*(C2:C100="F&quot)

Then change F for M for fellas and 1 for any of 2-7 for different grades

HTH
Geoff

 

[SteveAudus]

I typed in this formula
=SUM((E2:E202=3)*(U2:U202="F&quot)

To find out how many females got a 3,
and it returned

#VALUE!

What am I doing wrong?

The grade column includes only 1-7 and a few #N/A for missing grades.

Thanks for yout help
Steve Audus

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

http://www.educationinternet.co.uk

 

[Hasit]

Steve, when you typed in the formula, did you hit CTRL-SHIFT-ENTER?

This will set the formula up as an array (you'll get curly brackets around the formula {}), so the formula would look like this:

{=SUM((E2:E202=3)*(U2:U202="F&quot)}

 

[xlbo]

Yup, you'll get the VALUE! error if you don't enter it as an array ie instead of just pressing enter, hold down Ctrl+Shift+Enter
However, if you have #N/A! errors in the list, you'll need to modify the formula to:
=SUM((IF(ISNA(E2:E202),0,IF(E2:E202=3,1,0)))*(B2:B202="F&quot)

The 1st If checks for an #N/A! in the column, the 2nd If is the test for the grade - change the 3 in here to any of 1-7 for your different grades. The last section hasn't changed
REMEMBER, you must enter it with CTRL+SHIFT+ENTER
HTH
Geoff

 

[rekclaw]

I can't help but think this is the type of situation that Pivot Tables were created for. Have you tried that?

Rekclaw

 

[xlbo]

True - a pivot table would give a nice, easy set of answers but bear in mind the memory overhead associated with pivot tables pre XP. In XP, an OLAP cube is used to store the relationships between the data. In any earlier version, the data is stored on several "veryhidden" sheets and can easily bump up the size of a file by 2 - 5 x

Rgds
Geoff

 

[SteveAudus]

Thanks for your help,

=SUM((IF(ISNA(E2:E202),0,IF(E2:E202=3,1,0)))*(U2:U202="F&quot)

Worked great, but I'd like to be able to count the #N/A
also, any suggestions?

Also I did slip up with missing shift+ctrl+enter
What's the difference between a array and a normal formula?
Why the {Brackets}?

I have heard loads of people meation pivot tables,
what are they?

Cheers Again,
Steve Audus

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

http://www.educationinternet.co.uk

 

[xlbo]

For counting NAs , jsut use =Countif(E2:E202,"=#N/A!&quot

An array formula can test a condition on a large number of rows and returns a true / false (read 1 / 0) for each one. It builds up an internal array so for your formula, it would be something like

Grade Gender
1 x 0 = 0
1 x 1 = 1
0 x 0 = 0
1 x 0 = 0
1 x 1 = 1
etc etc
then all the results are summed to give you the COUNT of rows that meet BOTH conditions. If you wanted the total scores or soemthing, you would have another statement to multiply the array results by the actual score

Pivot Tables are a good way of aggregating and grouping data with the ability to drill down to varioous levels of detail.

HTH
Geoff

 

[SteveAudus]

xlbo (MIS)
"For counting NAs , jsut use =Countif(E2:E202,"=#N/A!&quot"

This will not work, I still need to know if the #N/A
is a Male or Female.

Thanks for your continued help with my problem

Steve Audus
Chaucer School

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

http://www.educationinternet.co.uk

 

[GlennUK]

to find number of #N/A's for M or F then use this formula ..


=SUM(ISNA(E2:E202)*(U2:U202="M&quot)

entered as an array formula, using CTRL-SHIFT-ENTER instead of ENTER.

Change "M" to "F" as required.

Glenn.

 

[xlbo]

Well, in that case, just use the original formula and substitute your grade for "#N/A!" Or
use
=SUM((IF(ISNA(E2:E202),1,0))*(U2:U202="F&quot)
HTH
Geoff