Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004

    Unanswered: Data Filter - Quantity of Records Shown?

    Does anyone know how to get excel to return a value for the number of records displayed in a Data Form with various Data Filters Active?

    I do a bi-monthly report on the quantity of items requiring input (blanks) in a Data Form database that my company uses.

    I run a filter to show blanks in the given column with "auto filter" on, but then I have to manually scroll and count the number of rows showing "blanks" to get the quantities for my report.

    There must be a way to get excel to automatically tell me how many rows are showing in a filtered data form.

    Any ideas?



  2. #2
    Join Date
    Jan 2004
    Aberdeen, Scotland
    Hi Aaron

    a couple of pointer for you.

    Excel does actually count the records automatically and will display this in the status bar, but this is really tempromental.

    If your report is always in the same format you could use a function to do this.

    Something like

    =SUMPRODUCT(--($A$2:$A$27 = "B")*($C$2:$C$27 = ""))

    Looking at what you have in your filters.

    or a dirty way of doing it is to apply your filters, in an empty column put in the value 1 and put in the function


    then delete the values in this column

    I used 27 rows in the dummy data i set up

    Hope this Helps


  3. #3
    Join Date
    Feb 2004

    Problem Solved


    I couldn't figure out your streamlined version, so I used the "dirty" solution, and it worked perfectly. I must say, if that was "dirty" you should see the rest of my spreadsheet functions - they're filthy!

    Thanks for your help!


Posting Permissions

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