filtering data for analysis

  • Thread starter Thread starter JVS
  • Start date Start date
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?
 
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
 
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
 
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.
 
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.
Click to expand...

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
 
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
Click to expand...
 
Back
Top