I need a mechanism to count rows based on several kinds of filters -
Every row represents one test I do in my labs.
There are several columns that represent the following:
Test serial number(number), date(date), name(string), lab name(string), result(number), test type(dropdown list), and several more of this kind.
This makes data entry easy.
Eventually I want to be able to count my tests based on filters I apply. For example, if I wanted to count every test that was done between 01/01/07 and 14/01/07 I can just type in the range and have it filter the counting.
The kind of filtering I need can even be complex, say a date range AND name AND result, sometimes downright random selection of filters.
My current mechanism is basically put array functions into cells along this line:
Howdy. The only other way (with formulas) is to use SUMPRODUCT, but you may not gain much in terms of speed. I changed a workbook that used array formulas like yours to SUMPRODUCT and it dropped the size of the workbook considerably and reduced cal time by 10 times. In your case it might prove helpful to at least try it.
Failing that, you might want to consider using VBA with array solutions, and then PASTE-SPECIAL. It might be worth considering in your case.
old, slow, and confused
but at least I'm inconsistent!