+ Reply to Thread
Results 1 to 9 of 9

Countif after autofilter

  1. 09-12-2013, 08:39 AM #1
    hommeg
    hommeg is offline
    Registered User
    Join Date
    09-10-2013
    Location
    washington, dc
    MS-Off Ver
    Excel 2010
    Posts
    10

    Countif after autofilter

    Hello,

    I am having trouble figuring out how to count the values in a column that are less than 100 after it has been autofiltered. The column (column E) contains only numbers, and is filtered down based on autofilters applied to columns A, B, C, and D. What I want to know is the number of values in column E that are less than 100. Normally (with no filters), I would just do =COUNTIF(E:E, "<100") and get my result, however this obviously doesn't work after autofiltering. I was hoping to use the subtotal function, but there isn't a countif option, I'm not sure how to approach this problem. Any help would be appreciated.

    Thank you.

    (using Excel 2010)
    Reply With QuoteRegister To Reply
  2. 09-12-2013, 08:44 AM #2
    Debraj Roy
    Debraj Roy is offline
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Countif after autofilter

    SUBTOTAL, Excel 2010.. has at-least 11 + 11 function.. to handle.. including COUNT

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Please Login or Register  to view this content.
    Regards!
    =DEC2HEX(3563)

    If you like someone's answer, click the star to give them a reputation point for that answer...
    Reply With QuoteRegister To Reply
  3. 09-12-2013, 08:46 AM #3
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Countif after autofilter

    Welcome to the forum.

    Try.

    Formula: copy to clipboard 
    Please Login or Register  to view this content.



    E1=Header.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.
    Reply With QuoteRegister To Reply
  4. 09-12-2013, 08:46 AM #4
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Countif after autofilter

    Hi,

    What you're asking for is achievable, though could I ask for a sample spreadsheet in which to trial potential solutions rather than having to create one from scratch?

    Thanks and regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/
    Reply With QuoteRegister To Reply
  5. 09-12-2013, 08:49 AM #5
    Debraj Roy
    Debraj Roy is offline
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Countif after autofilter

    oops.. Please ignore post # 2..

    I have skip this part..
    autofilters applied to columns A, B, C, and D
    fortis one will work for you..
    Reply With QuoteRegister To Reply
  6. 09-12-2013, 08:51 AM #6
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Countif after autofilter

    @Fotis

    Excellent solution.
    Reply With QuoteRegister To Reply
  7. 09-12-2013, 08:54 AM #7
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Countif after autofilter

    Quote Originally Posted by XOR LX View Post
    @Fotis

    Excellent solution.
    Thank you!
    Reply With QuoteRegister To Reply
  8. 09-12-2013, 09:05 AM #8
    Olly
    Olly is offline
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Countif after autofilter

    Change the range as appropriate, and enter as array formula (Ctrl-Shift-Enter):

    Please Login or Register  to view this content.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...
    Reply With QuoteRegister To Reply
  9. 09-12-2013, 09:33 AM #9
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Countif after autofilter

    @OllyXLS

    Why does that need to be array-entered? (cf Post#3 from Fotis)

    Regards
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] COUNTIF on an autofilter?
    By gordo in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-30-2018, 06:54 AM
  2. [SOLVED] Countif with Autofilter
    By ctrapper in forum Excel General
    Replies: 14
    Last Post: 05-30-2012, 01:35 PM
  3. Capturing the Excel AutoFilter Sorting Event-sort and autofilter options
    By Kognyto in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-11-2008, 05:36 PM
  4. [SOLVED] Subtotal on Autofilter with Countif
    By JavyD in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-22-2006, 03:50 PM
  5. countif for only visible rows when combined with autofilter - possible?
    By johli in forum Excel General
    Replies: 1
    Last Post: 09-21-2005, 04:05 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1