filtering data for analysis

J

JVS

I have a spreadsheet of students grades across different subjects with
formula rows below to total grades by subject, then convert totals to
percentages. I need to do the same analysis for male and female students
separately, then produce a summary in a separate worksheet. Is there a way
to do this using an IF formula to keep the three analyses (All, F, M) on the
same sheet, so that I can then create links to show the summary on a
different sheet?
 
P

Pete_UK

Suppose you have M or F in column B, with column C used for the first
subject. Suppose you have 100 rows of data, then you can put this
formula below your data (eg in cell C102):

=SUMIF($B$1:$B$100,"M",C$1:C$100)

to give the total marks for males, and in C103:

=SUMIF($B$1:$B$100,"F",C$1:C$100)

will give the total marks for females. Copy these two formulae across
the row for as many subjects as you have.

Hope this helps.

Pete
 
J

JVS

Thanks, Pete, however I think my original description may have been
misleading. It may have been clearer if I had said that I need not to
"total" grades but to count the number of each grade ie how many girls scored
grade A, how many grade B etc. I have tried to adapt your formula by using
COUNTIF instead os SUMIF, but I can't get it to work yet. Any suggestions
gratefully received. Thanks again!

JVS
 
P

Pete_UK

Okay, suppose you have this setup:

Name Gender Subject_1 Subject_2 etc

with headers in row 1 and grades of A, B, C etc shown below the
subjects. Your summary table would need to be in this format:

Gender Grade Subject_1 Subject_2 etc
F A
F B
F C
M A
M B
M C

and so on.
 
P

Pete_UK

Okay, suppose you have this setup:

Name      Gender     Subject_1     Subject_2    etc

with headers in row 1 and grades of A, B, C etc shown below the
subjects. Your summary table would need to be in this format:

Gender     Grade     Subject_1    Subject_2   etc
    F              A
    F              B
    F              C
    M              A
    M              B
    M              C

and so on.

Sorry, I accidentally clicked the Send button by mistake. Carrying
on ...

Suppose the headings for this summary table are in row 102, then put
this in C103:

=SUMPRODUCT(--($B$2:$B$100=$A103),--(C$2:C$100=$B103))

You will need to adjust the range and cell references to suit your
data, but ensure that you have the $ signs as shown. Then you can copy
the formula across the row to cover the number of subjects that you
have, and then you can copy those formulae down as required.

Hope this helps.

Pete
 
J

JVS

Thanks very much, Pete, that's great!

Pete_UK said:
Sorry, I accidentally clicked the Send button by mistake. Carrying
on ...

Suppose the headings for this summary table are in row 102, then put
this in C103:

=SUMPRODUCT(--($B$2:$B$100=$A103),--(C$2:C$100=$B103))

You will need to adjust the range and cell references to suit your
data, but ensure that you have the $ signs as shown. Then you can copy
the formula across the row to cover the number of subjects that you
have, and then you can copy those formulae down as required.

Hope this helps.

Pete
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top