Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summerising data
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summerising data
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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Emma" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MULTIPLE DATA - How to insert new data into existing data.... | Excel Discussion (Misc queries) |