I've been working on filtering a large set of data using a long COUNTIFS statement used in nearly 200 cells, counting probably a half million points of data, and it takes FOREVER to recalculate all the time. I'm working on making a user-defined function to begin streamlining things, and I can't seem to get it to work properly. I'll post the code I have so far; any help as far as syntax errors etc would be greatly appreciated. I will also post the workbook as well, in XLS format this time :P I've put in cell reference ranges to narrow down issues, but the range names are in the VBA for the time being.
[VBA]
Public Function OutRegEdRSP(value As Long) As Long
'This is the selected school code cell to look up
Dim SchoolCodeVar As Long
'These next four are defining the named ranges to use from CASEMIS sheet
Dim InRegClass As Range
Dim GradeCode As Range
Dim ExitDate As Range
Dim SchoolCode As Range
Set InRegClass = Worksheets("CASEMIS").Range("AQ14:AQ40000")
Set GradeCode = Worksheets("CASEMIS").Range("F12:F40000")
Set ExitDate = Worksheets("CASEMIS").Range("BG12:BG40000")
Set SchoolCode = Worksheets("CASEMIS").Range("AR12:AR40000")
value = Application.WorksheetFunction.CountIfs(SchoolCode, SchoolCodeVar, InRegClass, ">=80", _
GradeCode, "<>16", GradeCode, "<>17", GradeCode, "<>13", ExitDate, "")
End Function[/VBA]