Announcement

Collapse
No announcement yet.

Omit Hidden Rows When Counting

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Omit Hidden Rows When Counting



    In a database of names I use Filter- Advanced Filter - Unique records, to hide duplicated rows. Trouble is I don't know if there were any duplicated rows when it finishes. I would like to see the totals reflect this by not including them in the Countif function. Is it possible?

  • #2
    Re: Omit Hidden Rows When Counting (countif)

    There is a "SUBTOTAL" function which will count only visible items. However, it does not have a countif parameter.


    How are you hiding the duplicated rows? Do you have a flag value that indicates that a row is a duplicate?
    Regards,
    Barry

    My Favorite New Thing:
    Dynamic Named Ranges



    The alternative for
    "Press Any Key To Continue."

    and we all have one we'd like to use it on

    1. Cross Posting Etiquette
    2. Are You Here To Learn: What Have You Tried?
    3. Your Best Resource to begin learning VBA: RECORD A MACRO . . . Then Study It!

    Comment


    • #3
      Re: Omit Hidden Rows When Counting (countif)

      If you were using a formula like

      =COUNTIF(A1:A100,"x")

      then to count "x"s in unfiltered rows only try

      =SUMPRODUCT(--(SUBTOTAL(3,OFFSET(A1:A100,ROW(A1:A100)-ROW(A1)+1,,1))),--(A1:A100="x"))

      Comment


      • #4
        Re: Omit Hidden Rows When Counting (countif)

        With your Advanced Filter, you could "Copy to" instead of "Filter in Place", then there won't be any hidden rows.

        Comment


        • #5
          Re: Omit Hidden Rows When Counting (countif)

          Originally posted by daddylonglegs
          If you were using a formula like

          =COUNTIF(A1:A100,"x")

          then to count "x"s in unfiltered rows only try

          =SUMPRODUCT(--(SUBTOTAL(3,OFFSET(A1:A100,ROW(A1:A100)-ROW(A1)+1,,1))),--(A1:A100="x"))

          I think that DLL has got the better solution. I'd have suggested it but I've had difficulties on the intricasies of these compound sumproduct formulas.
          Regards,
          Barry

          My Favorite New Thing:
          Dynamic Named Ranges



          The alternative for
          "Press Any Key To Continue."

          and we all have one we'd like to use it on

          1. Cross Posting Etiquette
          2. Are You Here To Learn: What Have You Tried?
          3. Your Best Resource to begin learning VBA: RECORD A MACRO . . . Then Study It!

          Comment


          • #6
            Re: Omit Hidden Rows When Counting (countif)

            Sorry I had used Counta. Tried countif(C187:C247,">A"), returns Volatile with the formula cell blank.
            Auto Merged Post;

            Just tried iwrk..'s subtotal which does the trick. There are that many duplicates I had to double check!
            Thanks very much.
            Last edited by Diego Garcia; October 26th, 2007, 08:10. Reason: Auto Merged Doublepost

            Comment


            • #7


              If you wish to continue using this free service.

              Please be considerate of others who use the forum for searching. Your current Thread Title (which I will change) is non-reflective of your immediate problem.

              In future, please take 1 minute of your time to read the text on the New Thread page.

              Please take just 3 mins of your time to read: How To Get Your Question Answered...FAST! AND Anatomy Of A Good Thread Title

              REMEMBER: Your thread title should NEVER be what you THINK is your answer. 9 times out 10 it will wrong and prevent someone from finding a solution to a simliar issue.

              Comment

              Working...
              X