Summarize on 2 variables

[Steve M]

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.

 

[Pete_UK]

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

 

[Sean Timmons]

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.

 

[T. Valko]

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

 

[Steve M]

Thanks to all. Works great and now I understand the SUMPRODUCT
function. I'll try the INDEX function as well.