+ Reply to Thread
Results 1 to 8 of 8

How can I calculate the MODE in a filtered list? - SOLVED

  1. #1
    Registered User
    Join Date
    08-25-2010
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2007
    Posts
    52

    How can I calculate the MODE in a filtered list? - SOLVED

    There is no subtotal function for mode - does anyone have an idea on how to calculate the MODE that only calculates the filtered information?
    Last edited by o4008; 01-06-2011 at 02:57 PM.

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,664

    Re: How can I calculate the MODE in a filtered list?

    To do it with a helper column,

    =mode(if(B2:B100, A2:A100))

    ... confirmed with Ctrl+Shift+Enter, where B2 and down has the formula

    =subtotal(101, A2)
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    08-25-2010
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2007
    Posts
    52

    Re: How can I calculate the MODE in a filtered list?

    Where do I place the formulas? If my data is in fields Q21:Q5000, where do I put the helper and the subtotal formulas? Thank you so much - I cannot wait to try this out.

  4. #4
    Registered User
    Join Date
    08-25-2010
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2007
    Posts
    52

    Re: How can I calculate the MODE in a filtered list?

    Here is a sample worksheet. If the user filters on A4 (Yes or No) the fields B1:B3 should change based on filtered info. How do I do the mode?
    Attached Files Attached Files

  5. #5
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,664

    Re: How can I calculate the MODE in a filtered list?

    In C5 and copy down all the way to C5851,

    =SUBTOTAL(102, B5)

    In C3 (or anyplace)

    =MODE(IF(C5:C5851, B5:B5851))

    ... confirmed with CSE.

  6. #6
    Registered User
    Join Date
    08-25-2010
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2007
    Posts
    52

    Re: How can I calculate the MODE in a filtered list?

    It isn't working for me, I don't know what is missing or what I did incorrectly? See attached...
    Attached Files Attached Files

  7. #7
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,664

    Re: How can I calculate the MODE in a filtered list?

    The formula in C3 MUST be confirmed with Ctrl+Shift+Enter, not just Enter.

  8. #8
    Registered User
    Join Date
    08-25-2010
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2007
    Posts
    52

    Re: How can I calculate the MODE in a filtered list? - SOLVED

    Thank you! Using array formulas are brand new to me!

+ 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