Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    May 2001
    Location
    Berkeley, California, USA
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    counting unfiltered rows (97 SR2)

    I want to count the rows in a Database that are still visible after I have filtered out or hidden other rows.
    How do I do this?

    Alternatively, How can I count rows that have a given value in column D, and a Blank in Column A, and does not have a number in column B?

    Also, How can I filter out rows that contain a number in a given column?

  2. #2
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: counting unfiltered rows (97 SR2)

    =Subtotal(argument,range)
    Check help about.

  3. #3
    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: counting unfiltered rows (97 SR2)

    Since part 1 was answered (SUBTOTAL function), here is answer to 2nd part:
    1) if you want to filter first, filter and then use subtotal

    2) If you want to get the answer without filtering: (confirm with ctrl-shift-enter):

    <pre>=sum(if((d1:d100=GivenValue)*isblank(a1:A100) *not(isnumber(b1:B100)),1))</pre>


    change range as appropriate, Replace "GivenValue" with the given value or a cell containing the "given value"


    The 3rd question
    data - filter - autofilter
    select the column of interest
    custom
    select "not equal to"
    enter the number

    Steve

  4. #4
    3 Star Lounger
    Join Date
    May 2001
    Location
    Berkeley, California, USA
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: counting unfiltered rows (97 SR2)

    Thanks all.
    That almost does it.
    Steve: I guess I was not clear on the last question.
    I want to filter out all the rows that have any number in Column "C" and leave the rows that have values other than numbers in Column "C".

  5. #5
    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: counting unfiltered rows (97 SR2)

    Use the contains asterisk (*)

    autofilter
    Custom,
    contains (noquotes):
    "*"

    Thus should only display TEXT.
    Conversely does not contain "*" will show only the numbers

    FYI in case you are curious, True/False are considered NUMBERS

    Steve

  6. #6
    3 Star Lounger
    Join Date
    May 2001
    Location
    Berkeley, California, USA
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: counting unfiltered rows (97 SR2)

    This almost worked except that it also filtered out the blank cells. I guess it considers a blank a number.

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

    Re: counting unfiltered rows (97 SR2)

    If

    "Alternatively, How can I count rows that have a given value in column D, and a Blank in Column A, and does not have a number in column B?"

    must be done after filtering, then:

    =SUMPRODUCT(SUBTOTAL(3,OFFSET($D$2:$D$100,ROW($D$2 :$D$100)-MIN(ROW($D$2:$D$100)),,1))*($D$2:$D$100=aGivenValu e)*($A$2:$A$100="")*(1-ISNUMBER($B$2:$B$100)))

    Adjust ranges to suit. BTW, the ranges are the ranges which you are filtering.
    Microsoft MVP - Excel

  8. #8
    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: counting unfiltered rows (97 SR2)

    The filter finds cells with text in it, a blank has none. The blank is NOT a number, it is also NOT text.

    This should work:
    Custom
    equals(no quotes):
    "*"
    OR
    equals
    [leave it blank]

    Steve

Posting Permissions

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