• count in filtered list (2000sr1)

    Author
    Topic
    #390103

    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!

    Viewing 0 reply threads
    Author
    Replies
    • #691692

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

      Steve

      • #691721

        Thanks, Steve.
        So if the existing formula, which counts everything whether it’s filtered or not, is
        =COUNTIF(D$18:D$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.

        • #691727

          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%5B/url%5D

          Steve

        • #691810

          =SUMPRODUCT(SUBTOTAL(3,OFFSET(D$18:D$2313,ROW(D$18:D$2313)-MIN(ROW(D$18:D$2313)),,1))*(D$18:D$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.

          • #697521

            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.

    Viewing 0 reply threads
    Reply To: count in filtered list (2000sr1)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: