Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2007

    Unanswered: Complex counting of rows?

    Would like some suggestions please.

    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:
    {=SUM( (B2:B40000 >= begindate)*(B2:B40000 < enddate)*...  )}
    and keep multiplying by more filters as needed.

    This mechanism had me create many array functions to be put in cells to have a clear page of statistics to print out, which means things got very slow to update, and very hard to manage.

    So please, if anyone knows of a better way to deal with such a task I'd love to hear!


  2. #2
    Join Date
    Oct 2003
    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!

    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Nov 2007
    Thank you, I will try it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts