Summerising data

[Emma]

Hi,
I have a spread sheet with 3 columns, category, number of students, & month
i need to summerise the sheet into number of students per category, number of
students per month overall & then i need it split number of students per
category per month. i use sumif for the first two and that works fine but i
cant seem to get anything to work to sum the number of student dependant on
two crtieria. For month i have been using =month() instead of doing bewteen 2
dates to simplfy the table.

e.g
summary
09 10 11 total
a 2 101 0 103
b 56 0 5 61
c 43 20 18 81
total 101 121 23 245

a 12 10
a 2 09
a 89 10
b 5 11
b 56 09
c 23 09
c 20 09
c 18 11
c 20 10

As i said the end totals are fine with sumif but need a function/formula for
the centre of the table, iv tried various things and cant get any to work,
sumproduct, sumif, dsum dont seem to return correct answers or just return
errors please advise.

 

[Max]

Assuming your source data is running in cols A to C,
from row1 down, viz:

a 12 10
a 2 09
a 89 10
b 5 11

etc

and you have listed in I1 across: 9,10,11
and listed in H2 down: a,b,c

Put in I2:
=SUMPRODUCT(($A$1:$A$100=$H2)*($C$1:$C$100=I$1),$B$1:$B$100)
Copy across and fill down to populate