Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    count records in filtered list (xl2k)

    I need to count the number of records in a filtered list. I think I'm missing something.
    A way which works, but is very clunky, is to put a formula like
    =AND(H13=TRUE,I13=TRUE,J13=FALSE,K13=TRUE,M13<50)
    at the end of each row (in column N in my case) and then use the countif function to count the number of records for which all the criteria are met, in other words, rows for which the AND formula returns the value TRUE.
    A simpler, cleaner, way, please?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: count records in filtered list (xl2k)

    The builtin SUBTOTALS functions count, average, sum, etc directly the visible items in a filtered list.
    =Subtotal(2,A1:A1000)

    will count the visible items in A1:A1000.

    If you really need a multicondition countif you would have to use an ARRAY function and I would need more information to provide it. Chip Pearson has a primer on them Array Formulas

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count records in filtered list (xl2k)

    Thanks very much, Steve. And that will cope with criteria involving Or and Not much more simply than my brute-force thing.
    I'll add SUBTOTAL in all 11 flavours to my list of must-have functions...
    Thanks again.

Posting Permissions

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