A rather nasty COUNTIFS statement issue

[fonix]

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]

[fonix]

Here's a bit of clarification on the countifs statement and what I'm trying to accomplish:
I'm trying to select all the entries on the CASEMIS tab that match the school code using the value in another cell (which is what SchoolCodeVar is supposed to be ), selecting for entries that are greater or equal to 80 in the InRegClass column, and removing entries with 13,16 and 17 in the grade code column, and finally selecting all blank entries in the ExitDate column

[Bob Phillips]

Use helper columns

[fonix]

Another update; here's what I've got... it doesn't evaluate to a #VALUE error anymore, just doesn't work :P I've commented out the range variables and just put in references to help narrow down the issue

[VBA]'This is the selected site code cell to look up
Dim SchoolCodeVar As Long

'This is the big, ugly COUNTIFS statement to count all students in regular class
'more than or equal to 80 percent of the time, excluding grade codes 16,17 and 13
'and removing all students with entries in the exit date column

ReturnValue = Application.WorksheetFunction.CountIfs(Worksheets("CASEMIS").[AR:AR], _
SchoolCodeVar, Worksheets("CASEMIS").[AQ:AQ], ">=80", _
Worksheets("CASEMIS").[F:F], "<>16", Worksheets("CASEMIS").[F:F], _
"<>17", Worksheets("CASEMIS").[F:F], "<>13", Worksheets("CASEMIS").[BG:BG], "= ")


End Function[/VBA]

[fonix]

I'm sorry, I'm trying to make a VBA UDF for those COUNTIFS statements, I've got the normal COUNTIFS working.

[Bob Phillips]

But you said the problem was slowness, so I gave you an un-normal COUNTIFS. It should be a lot faster.

[fonix]

I posted a much-slimmed version of the workbook we're using, and a few UDFs would help both usability (used by a few dozen people) and accuracy (one of the biggest issues with this workbook are the COUNTIFS statements), as well as speed.

[Bob Phillips]

Have you even looked at what I did, usability and speed are improved. My guess is that your VBA will run like a dog.

[fonix]

I did, and we were doing something similar in the past before I was added to the project, and it was scrapped because of, you guessed it, usability issues. I personally would use an approach like this, but I've been asked to make it even easier, if at all possible. It may be that I can use evaluation columns like this on a hidden sheet, but I would still need to create a simpler formula, which just brings me back to my original issue. I appreciate the input, but I'm constrained to a UDF for the time being.

[Shred Dude]

You might want to abandon the CountIFs approach and use Advanced Filter. It is very fast on large data sets.

If your objective is to merely count the number of resulting matches, you don't even need to do what I've done in the workbook for illustrative purposes.

Putting your criteria in a range on the Advanced Filter sheet I added, and clicking the Filter button will deliver a filtered list to the Advanced filter page and report the number of matches.

You could put your real data set on the CASEMIS sheet and test it. I've created dynamic ranges to capture all of your data on that sheet. Be careful not to delete the references in the dynamic range myData on the CASEMIS sheet.

Attached is a workbook with an example of using Advanced filter. Very Fast!

[fonix]

I definitely agree about Filters (didn't know about advanced filters though... awesome! Thanks for that, should make other parts of the workbook more streamlined), we use them in our workbooks all the time for small tables. However, the example file I've posted is one row of several hundred, in the same table, in a typical worksheet, and sometimes there are several of these worksheets in a workbook. I'm attempting to streamline the whole process by narrowing down the data needed and calculated, but that is mainly a separate issue. The issue at hand, still, is a usable, uncomplicated UDF to calculate a fairly complex (apparently, at least; I get at least 2 or 3 complaints a day about the various COUNTIFS used in the full workbook) COUNTIFS statement. If I can get this one working, I should be able to streamline the rest of the workbook using a combination of Advanced filters and logic tables, but the UDF question still stands.

[Shred Dude]

You can incorporate the advanced filter into a UDF, and use the resulting range as the basis for your counting, as I did in the little message box in the previous example.

eg: = myUDF(mydatarange, mycriteriarange) could return the number of matches.

You could also compound the Advanced Filters into multiple stages, taking data from multiple sheets and continuing to filter down the filtered results.

If your goal is merely to count the resulting matches of a multi-variable criteria like you've stated, I think Advanced Filter could work for you.

Good luck with it.

[Shred Dude]

Here's a workbook with UDF that counts the number of records that match the supplied criteria. See results on CASEMIS sheet.

Changing Criteria on Advanced Filter sheet will cause a recalc of the UDF.

syntax:

[VBA]countMatches(mydata,mycriteria)[/VBA]

[fonix]

Interesting... I'll experiment with this a bit to make it more extensible; I'll need to pass the criteria to the filter from each cell instead of centrally, but that should be no problem really. Thanks for the input ShredDude... I don't have much experience with VBA. I didn't know that range and sheet references weren't case-sensitive. Anyone have any input on the CountIfs issue still? I would like to play with that just to get it working and compare different approaches for future use.

[Shred Dude]

Looking at your Countifs code I would wonder about the accuracy of the last element. It would depend on the consistenty of your data. If the objective of "removing all students with entries in the exit date column", is the same thing as counting the ones that don't have anything in the exit date column then instead of trying to test for a string of a certain number of blanks with[VBA] Worksheets("CASEMIS").[BG:BG], "= ") [/VBA], perhaps you could instead try to include all records that are empty with "=".

Try this:


[VBA]ReturnValue = Application.WorksheetFunction.CountIfs(Worksheets("CASEMIS").[AR:AR], _
SchoolCodeVar, Worksheets("CASEMIS").[AQ:AQ], ">=80", _
Worksheets("CASEMIS").[F:F], "<>16", Worksheets("CASEMIS").[F:F], _
"<>17", Worksheets("CASEMIS").[F:F], "<>13", Worksheets("CASEMIS").[BG:BG], "=") [/VBA]

[fonix]

I was experimenting with that by referring to working COUNTIFS formulas that used a certain number of empty spaces; at times, the COUNTIFS weren't working unless I had some arbitrary (I want to say 9) spaces, even though the cells themselves were blank with no values. I think instead I'll insert a logical value 0 in the empty cells beforehand to make that much simpler.

[Bob Phillips]

I think instead I'll insert a logical value 0 in the empty cells beforehand to make that much simpler.

To improve usability?