Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    count in filtered list (2000sr1)

    How can I make some COUNTIF statements referring to column, say, A, count only the matching cells when the list is filtered by one or more criteria in other columns in the list?
    Thanks in advance!

  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 in filtered list (2000sr1)

    Use SUBTOTAL if you are only basing it on the filtering in place. Subtotal uses ONLY the visible data.

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count in filtered list (2000sr1)

    Thanks, Steve.
    So if the existing formula, which counts everything whether it's filtered or not, is
    =COUNTIF(D$18$2313,"1")
    (and subsequent cells look for the value "2" and so on), how do I combine this with the SUBTOTAL trick, preferably in a way which tolerates the filter option 'show all', ie no filter at all?
    I figure I need a "SUBTOTAL IF" function, which only exists in my head!
    It comes from a survey generating responses from 1 to 5, and I want to produce reports deriving from particular districts, age groups, and so on.

  4. #4
    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 in filtered list (2000sr1)

    This sounds like you might want to do a PIVOT TABLE of your data. This will summarize and sum/count/average, etc based on various combinations of your data.

    There is NO SUBTOTALIF. You can use subtotal which can count, sum, average, etc the "visible" items in a filtered list (this is "live" when the filters change the subtotals will change) and also include the count, sum, average,etc functions to get ALL the data to compare them directly.

    You can create multiconditional IFs (like countif) but they include average, var, min, max, etc using Array formulas:
    See Chip Pearson's site for a "tutorial"
    http://www.cpearson.com/excel/array.htm

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count in filtered list (2000sr1)

    =SUMPRODUCT(SUBTOTAL(3,OFFSET(D$18$2313,ROW(D$18$2313)-MIN(ROW(D$18$2313)),,1))*(D$18$2313=1))

    I assumed the criterion (1) to be a number, not a text-formatted number ("1"). You can even have this criterion in a cell of its own and reference that cell.
    Microsoft MVP - Excel

  6. #6
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count in filtered list (2000sr1)

    Sorry late in thanking for these, chaps.
    This job no longer top of the pile.
    Will come back on this topic if need to.
    Thanks again, very much.

Posting Permissions

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