Excel Novice: Would like to define CountIFS Critera as a variable array.

[Nementh]

Hello all,

I've learned a lot over the years with excel, however I'm at a standstill on a particular problem. Long story short, I work for a school district, and need to provide total number of students each teacher has during any given day from our student information system.

The fastest way for me to export the data is to export all student schedules, and use countif to find how many a teacher appears in the raw data, the problem this year, is I've been asked to exclude students from some courses, and said courses may change per request of the report.

What I have so far is:

=SUM(COUNTIFS('HS Schedules'!O:O,I3,'HS Schedules'!N:N,INDIRECT(F1)))

'HS Schedules'!O:O = 6th hour teachers
I3 = teacher name.
'HS Schedules'!N:N = 6th hour classes
F1 = list of excluded courses I don't want to count.

F1 is built by a user placing a mark in a column, that then concatenates the value into a string. I concatenate the string result and format it as {"Footbal", "Golf"}

I can paste the value of F1 into

=SUM(COUNTIFS('HS Schedules'!O:O,I3,'HS Schedules'!N:N,{"Footbal", "Golf"}))

and it works, however I'm stuck at getting the formula to read the text value for the criteria

Open to thoughts or different suggestions.

Due to student data I cannot provide the worksheets, however; they are arranged as such:

HS Schedules

A	B	C	...	N	O
Student #	LastFirst	Period1	...	Period 6 Course	Period 6 Teacher
123456	lastfirst	2D Art	...	Football	Teacher_1

<tbody>
</tbody>

 

[Peter_SSs]

Welcome to the MrExcel board!

F1 is built by a user placing a mark in a column, ..

1. What 'mark'?
2. What column?
3. What sheet?
4. What column are the courses in that the 'mark' is placed next to?
5. You say that F1 is a list of excluded courses, but =SUM(COUNTIFS('HS Schedules'!O:O,I3,'HS Schedules'!N:N,{"Footbal", "Golf"})) indicates that the list is being included. Can you clarify?

 

[Nementh]

Sorry Peter I didn't include that info. Here's what generates F1, and everything else on that sheet for reference.

	A	B	C	D	E	F	G	H	I	J	K
1			"		=H3&H4&H5...&H421	=CONCATENATE("{",LEFT(E1,LEN(E1)-1),"}")
2	Teacher	# of Students		Exclude	Course	Students			Teacher	# of Students	Excluded
3	Last_First	=COUNTIF('HS Schedules'!A:O,A3)			Art	=COUNTIF('HS Schedules'!A:CA,E3)		=IF(D3="","",CONCATENATE($C$1,E3,$C$1,";"))	=A3	=B3	=SUM(COUNTIFS('HS Schedules'!O:O,I3,'HS Schedules'!N:N,INDIRECT(F1)))
4	Last_First_1	=COUNTIF('HS Schedules'!A:O,A4)		X	Football	=COUNTIF('HS Schedules'!A:CA,E4)		=IF(D4="","",CONCATENATE($C$1,E4,$C$1,";"))	=A4	=B4	=SUM(COUNTIFS('HS Schedules'!O:O,I4,'HS Schedules'!N:N,INDIRECT(F1)))

<tbody>
</tbody>

There's probably a better way to do D:H, but it was the fast way to do it. there are roughly 400 courses total, and 20-30 that are excluded, but for flexibility, I need to be able to include and exclude courses at various parts of the year.

If I paste F1's result {"Football","Lifetime Sports","Marching Band","Physical Fitness"} for example, in place of INDIRECT(F1) In K:K everything works as expected. I'd just prefer to make it work without having to paste, so I can send the workbook out to the people who analyze the data.

A:B will be moved to I:K later, they were my original #'s without excluded courses from last years data set.

 

[Eric W]

Here's one way to do it. Let me know if my sample sheets don't match yours.

The HS Schedules sheet:

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Student #	LastFirst	Period1											Period 6 Course	Period 6 Teacher
2	123456	lastfirst	2D Art											Football	Doe_John
3	111	aaa												Football	Doe_John
4	222	bbb												Golf	Roe_Mary
5	333	ccc												Art	Doe_John
6	444	ddd												Chemistry	Roe_Mary
7	555	eee												Golf	Roe_Mary
8	666	fff												Football	Doe_John
9	777	ggg												Football	Smith_Ann
10	888	hhh												Golf	Doe_John
11	999	iii												Chemistry	Smith_Ann

<tbody>
</tbody>

HS Schedules​

The teacher sheet:
Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1														Football	2
2	Teacher	# of students		Exclude	Course	Students			Teacher	# of students	Excluded			Golf
3	Roe_Mary	3			Art	1			Roe_Mary	3	2
4	Doe_John	5		X	Football	4			Doe_John	5	4
5	Smith_Ann	2		X	Golf	3			Smith_Ann	2	1
6	Jones_Bob	0			Physics	1			Jones_Bob	0	0

<tbody>
</tbody>

Teacher List​

Worksheet Formulas Cell Formula J3 =B3 O1 =COUNTIF(D1:D500,"X") <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula K3 {=SUM(COUNTIFS('HS Schedules'!O:O,I3,'HS Schedules'!N:N,OFFSET('Teacher List'!$N$1,0,0,'Teacher List'!$O$1,1)))} N1 {=IFERROR(INDEX(E:E,SMALL(IF(D3:D500="X",ROW(D3:D500)),ROW())),"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

I removed your E1, F1, and H formulas, and replaced them with a list in N. You can put this in H if you want, now that it's available. The N list contains the excluded courses. The O1 cell contains the number of excluded courses. Put the formula in N1, confirm it with Control-Shift-Enter. Then copy the cell and paste it down as far as necessary. Then put in the O1 formula. Then put in the K3 formula, confirm with Control-Shift-Enter, and paste it down the column.

Let me know if this works for you.

Edit: I have heard that the IFERROR function is fairly inefficient, and in a large spreadsheet might slow it down. The N1 formula can be replaced with:

=IF(ROW()<=$O$1,INDEX(E:E,SMALL(IF(D3:D500="X",ROW(D3:D500)),ROW())),"")

and confirm with Control-Shift-Enter. Then use it the same way.

 

[Nementh]

Thanks Eric,

That did exactly what I needed it to. it also taught me a lot about Excel.

 

[Eric W]

Glad to help! I'm constantly learning new things about Excel, that's one reason I like it!