Summarize on 2 variables
Hoping someone can help.
I have a spreadsheet containing students from 4 schools, thru cell 2159, sorted by Student-ID (6 digit number) and last name. I would like a summary report showing the 4 schools with each of 3 status, free,reduced,full. Within school 105 (column A) total all students whose status is Free, status is reduced, status is full. Do for all 4 schools. 105 Free 999 Reduced 999 Full 999 110 Free 999 Reduced 999 Full 999 510 Free 999 Reduced 999 Full 999 710 Free 999 Reduced 999 Full 999 I am currently using a pivot table to show these totals but would like to have a 'table' at the end of the worksheet showing these as well. Thanks in advance. |
Summarize on 2 variables
Set up your table like this (with the school number in column A on
each row of the table): School Status Number 105 Free 105 Reduced 105 Full 110 Free 110 Reduced 110 Full 510 Free 510 Reduced 510 Full 710 Free 710 Reduced 710 Full Suppose the Label row is 2200, then put this formula in C2201 under Number: =SUMPRODUCT((F$1:F$2159=A2201)*(S$1:S$2159=B2201)) This assumes that the feeder school code is in column F and the status is column S - adjust to suit your data. Then you can copy the formula down to C2211. Hope this helps. Pete On Sep 26, 4:36*pm, Steve M wrote: Hoping someone can help. I have a spreadsheet containing students from 4 schools, thru cell 2159, sorted by Student-ID (6 digit number) and last name. *I would like a summary report showing the 4 schools with each of 3 status, free,reduced,full. Within school 105 (column A) total all students whose status is Free, status is reduced, status is full. *Do for all 4 schools. 105 Free * *999 * * Reduced 999 * * Full * *999 110 Free * *999 * * Reduced 999 * * Full * *999 510 Free * *999 * * Reduced 999 * * Full * *999 710 Free * *999 * * Reduced 999 * * Full * *999 I am currently using a pivot table to show these totals but would like to have a 'table' at the end of the worksheet showing these as well. Thanks in advance. |
Summarize on 2 variables
Assuming the following column order:
col A col B col c col d ---------- ----------- ----------- ---------- School # student ID last name meal type with 5000 rows of data and the table is at row 5050: col A col b col c ------ ------- ------------------ 105 Free =sumproduct(--($A$2:$A$5000=A5050),--($D$2:$D$5000=B5050)) will count all students in school 105 with Free lunch. "Steve M" wrote: Hoping someone can help. I have a spreadsheet containing students from 4 schools, thru cell 2159, sorted by Student-ID (6 digit number) and last name. I would like a summary report showing the 4 schools with each of 3 status, free,reduced,full. Within school 105 (column A) total all students whose status is Free, status is reduced, status is full. Do for all 4 schools. 105 Free 999 Reduced 999 Full 999 110 Free 999 Reduced 999 Full 999 510 Free 999 Reduced 999 Full 999 710 Free 999 Reduced 999 Full 999 I am currently using a pivot table to show these totals but would like to have a 'table' at the end of the worksheet showing these as well. Thanks in advance. |
Summarize on 2 variables
Is this how your pt is setup:
........A........B................C 2...105....Free............999 3............Reduced......999 4............Full..............999 5...110...Free............999 6............Reduced.....999 7............Full.............999 Assume the range is A2:C13 Create headers for the other table like this: ...........F...........G...........H.............. .I 1...................Free.....Reduced.......Full 2.....105......................................... ..... 3.....110......................................... ..... 4.....510......................................... ..... 5.....710......................................... ..... Enter this formula in G2: =INDEX($C$2:$C$13,MATCH($F2,$A$2:$A$13,0)+COLUMNS( $G2:G2)-1) Copy across then down as needed -- Biff Microsoft Excel MVP "Steve M" wrote in message ... Hoping someone can help. I have a spreadsheet containing students from 4 schools, thru cell 2159, sorted by Student-ID (6 digit number) and last name. I would like a summary report showing the 4 schools with each of 3 status, free,reduced,full. Within school 105 (column A) total all students whose status is Free, status is reduced, status is full. Do for all 4 schools. 105 Free 999 Reduced 999 Full 999 110 Free 999 Reduced 999 Full 999 510 Free 999 Reduced 999 Full 999 710 Free 999 Reduced 999 Full 999 I am currently using a pivot table to show these totals but would like to have a 'table' at the end of the worksheet showing these as well. Thanks in advance. |
Summarize on 2 variables
Thanks to all. Works great and now I understand the SUMPRODUCT
function. I'll try the INDEX function as well. On Fri, 26 Sep 2008 10:36:55 -0500, Steve M wrote: Hoping someone can help. I have a spreadsheet containing students from 4 schools, thru cell 2159, sorted by Student-ID (6 digit number) and last name. I would like a summary report showing the 4 schools with each of 3 status, free,reduced,full. Within school 105 (column A) total all students whose status is Free, status is reduced, status is full. Do for all 4 schools. 105 Free 999 Reduced 999 Full 999 110 Free 999 Reduced 999 Full 999 510 Free 999 Reduced 999 Full 999 710 Free 999 Reduced 999 Full 999 I am currently using a pivot table to show these totals but would like to have a 'table' at the end of the worksheet showing these as well. Thanks in advance. |
All times are GMT +1. The time now is 03:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com