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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]...[/TD]
[TD]N[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]Student #[/TD]
[TD]LastFirst[/TD]
[TD]Period1[/TD]
[TD]...[/TD]
[TD]Period 6 Course[/TD]
[TD]Period 6 Teacher[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]lastfirst[/TD]
[TD]2D Art[/TD]
[TD]...[/TD]
[TD]Football[/TD]
[TD]Teacher_1[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.


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



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
aaa	Football
bbb	Golf
ccc	Art
ddd	Chemistry
eee	Golf
fff	Football
ggg	Football
hhh	Golf
iii	Chemistry

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Student #[/TD]
[TD="bgcolor: #FAFAFA"]LastFirst[/TD]
[TD="bgcolor: #FAFAFA"]Period1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Period 6 Course[/TD]
[TD="bgcolor: #FAFAFA"]Period 6 Teacher[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]123456[/TD]
[TD="bgcolor: #FAFAFA"]lastfirst[/TD]
[TD="bgcolor: #FAFAFA"]2D Art[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Football[/TD]
[TD="bgcolor: #FAFAFA"]Doe_John[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]111[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #FAFAFA"]Doe_John[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]222[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #FAFAFA"]Roe_Mary[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]333[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #FAFAFA"]Doe_John[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]444[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #FAFAFA"]Roe_Mary[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]555[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #FAFAFA"]Roe_Mary[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]666[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #FAFAFA"]Doe_John[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]777[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #FAFAFA"]Smith_Ann[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]888[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #FAFAFA"]Doe_John[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]999[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #FAFAFA"]Smith_Ann[/TD]

</tbody>

HS Schedules​

The teacher sheet:
Excel 2010

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

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

</tbody>

Teacher List​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]J3[/TH]
[TD="align: left"]=B3[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]O1[/TH]
[TD="align: left"]=COUNTIF(D1:D500,"X")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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



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!