Count names based on enrollment

[paulkc]

I'm trying to process a lot of student data to gather retention data from year to year. Columns A and B contain the students first and last name. I'm attaching both sheets of my workbook to illustrate what I'm doing. My goal is to use the 2nd sheet to analyze the data on the first sheet.

Under each year, there are 4 data points I'm currently working on:

1. Count of the students enrolled in each grade (columns c & g)
2. Count of the students who were in the next grade in the following year (columns d & h)
3. Count of the students who were in the same grade the following year (columns e & i)
4. Count of the students who were not enrolled in any grade in the following year (columns f & j)

In Step 2, I'm using the SumProduct function (this is my first experience with this function so I'm not sure I completely understand it's proper usage) to determine the names who were in the following grade in the next year. I think the issue lies in my not understanding how the function treats blank cells vs a "0" in the cell contents. The function seems to work fine on other grades but I use zero to designate Kindergarten while other numbers represent the 1st, 2nd, etc grades. If you will look at D4 it shouldn't be larger than C4 but I know it has something to do with Row 10 on the "Student List" worksheet because this student was not enrolled in 0 in 1987 but was enrolled in 1 in 1988.

Student Retention Worksheet - Public.xlsx
	A	B	C	D	E	F	G	H
1	First	Last	Dup Proof	1987				1988
2	Fname1	Lname1	Unique	0				1
3	Fname2	Lname2	Unique					2
4	Fname3	Lname3	Unique	1				2
5	Fname4	Lname4	Unique	1				2
6	Fname5	Lname5	Unique	2				3
7	Fname6	Lname6	Unique	3
8	Fname7	Lname7	Unique	1
9	Fname8	Lname8	Unique	1				1
10	Fname9	Lname9	Unique					1
11	Fname10	Lname10	Unique					0
12	Fname11	Lname11	Unique	1				2
Student List

Cell Formulas
Range	Formula
C2:C12	C2	=IF(COUNTIFS($A$2:$A$10011,$A2,$B$2:$B$10011,$B2)>1,"Duplicate","Unique")

Student Retention Worksheet - Public.xlsx
	A	B	C	D	E	F	G	H	I	J
1			1987				1988
2			Totals	Promoted	Retained	Turnover	Totals	Promoted	Retained	Turnover
3	Total Students		8	6	1	1	9	0	0	9
4	0		1	2	0	-1	1	0	0	1
5	1		5	3	1	1	3	0	0	3
6	2		1	1	0	0	4	0	0	4
7	3		1	0	0	1	1	0	0	1
8	4		0	0	0	0	0	0	0	0
9	5		0	0	0	0	0	0	0	0
10	6		0	0	0	0	0	0	0	0
11	7		0	0	0	0	0	0	0	0
12	8		0	0	0	0	0	0	0	0
13	9		0	0	0	0	0	0	0	0
14	10		0	0	0	0	0	0	0	0
15	11		0	0	0	0	0	0	0	0
16	12		0	0	0	0	0	0	0	0
Totals

Cell Formulas
Range	Formula
C3:J3	C3	=SUM(C4:C16)
C4:C16,G4:G16	C4	=COUNTIF('Student List'!D$2:D$10011,$A4)
D4:D16,H4:H16	D4	=SUMPRODUCT(--('Student List'!D$2:D$10011=$A4),--('Student List'!H$2:H$10011='Student List'!D$2:D$10011+1))
E4:E16,I4:I16	E4	=COUNTIFS('Student List'!D$4:D$10011,$A4,'Student List'!H$4:H$10011,$A4)
F4:F16,J4:J16	F4	=C4-D4-E4

​

​

 

[Phuoc]

Try change:

=SUMPRODUCT(--('Student List'!D$2:D$10011=$A4),--('Student List'!H$2:H$10011='Student List'!D$2:D$10011+1))

to:

=COUNTIFS('Student List'!D$2:D$10011,$A4,'Student List'!H$2:H$10011,A4+1)

 

[paulkc]

Thank you! I don't know why I couldn't get that. Sometimes I can't see the forest for the trees.

Do you (or anyone else) have a suggestion on how to eliminate the blank columns on the "Student List" sheet? I'm having to leave them blank because when I copy the 4 columns (C-F) on the "Totals" worksheet to the next year, it advances all the column references by 4. This is fine for the references on that worksheet. But the references for the other sheet, only need advancing by 1. If adding the blank columns is the best way to do that, I don't mind but I figure I'm missing a simpler way.