+ Reply to Thread
Results 1 to 3 of 3

using the Small function on a filtered dataset

  1. #1
    Registered User
    Join Date
    06-01-2012
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 2003
    Posts
    1

    using the Small function on a filtered dataset

    Good evening - I have been trying to use the Small function on a filtered dataset, but I return all results. I am trying to limit the result set to only those filtered values. Example below:
    Unfiltered:
    Marty 4 4 4
    Patrick 4 4 3
    Steve 3 4 2
    Walt 3 4 4
    Carlos 4 5 3

    Formula - Small(B1:B5,1) Should return results of 3, 4, 2

    Filtered
    Marty 4 4 4
    Carlos 4 5 3

    Formula - Small(B1:B5,1) still returns the results of 3,4,2 - but I am trying to get the results to be 4,4,3. I know there needs to be some sort of Sumproduct, but it doesnt seem to be working.

    Please help and thanks for this forum, I have been googling everywhere to no avail

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: using the Small function on a filtered dataset

    Hi hmbrglar,

    Welcome to the forum.

    Small (b1:b5,1) would always return one value... and you need 3,4,2 ?
    Subtotal is the function which works on filtered values... suggest you to upload a sample workbook. thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,586

    Re: using the Small function on a filtered dataset

    Use advanced filter and use copy to another location, for Criteria use helper cells .Use formula for Range used for filtered data.
    Last edited by kvsrinivasamurthy; 06-02-2012 at 06:12 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1