Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: Omit Hidden Rows When Counting

  1. #1
    Join Date
    27th February 2003
    Posts
    61

    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?

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    22nd January 2004
    Location
    Colorado Springs
    Posts
    3,992

    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!

  3. #3
    Join Date
    24th April 2007
    Location
    England
    Posts
    675

    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"))

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    14th July 2007
    Posts
    759

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    22nd January 2004
    Location
    Colorado Springs
    Posts
    3,992

    Re: Omit Hidden Rows When Counting (countif)

    Quote 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!

  6. #6
    Join Date
    27th February 2003
    Posts
    61

    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 at 08:10. Reason: Auto Merged Doublepost

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,716
    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.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Copy Hidden Rows & Keep Hidden When Pasting`
    By simmon in forum EXCEL HELP
    Replies: 1
    Last Post: June 8th, 2007, 21:25
  2. Replies: 2
    Last Post: May 17th, 2006, 07:29
  3. copy skipping hidden rows or hidden columns value
    By ilyaskazi in forum EXCEL HELP
    Replies: 4
    Last Post: February 25th, 2006, 17:13
  4. Which Rows Are Hidden?
    By excelboy in forum EXCEL HELP
    Replies: 6
    Last Post: November 10th, 2005, 06:19
  5. hidden rows
    By Laura Holcombe in forum EXCEL HELP
    Replies: 1
    Last Post: October 7th, 2004, 02:16

Bookmarks

Posting Permissions

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