+ Reply to Thread
Results 1 to 11 of 11

Subtotal with Countif

  1. #1
    Registered User
    Join Date
    11-23-2010
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2003
    Posts
    5

    Subtotal with Countif

    Hi

    I was wondering if anyone can help... I need to use countif but be able to filter as well... this is my formula

    COUNTIF(B$51:B$124,">"&B40)

    I've tried...SUMPRODUCT(SUBTOTAL(3,OFFSET(b51,ROW(b51:b124)-ROW(b51),0)),--(b51:b124,">&b40")) but it won't work. What am I doing wrong?

  2. #2
    Registered User
    Join Date
    05-21-2013
    Location
    UK
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    86

    Re: Subtotal with Countif

    What is it you're trying to filter by?

    The second part of COUNTIF is filtering the data set by the criteria. If you need more criteria that can be done (easily in Excel 2007+ with COUNTIFS). If you're referring to other types of filters I'm unsure what you mean.

    Can you give examples of your inputs and desired output?
    If I've helped or inspired, please click the star.

  3. #3
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Subtotal with Countif

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

    This should do...
    Regards,
    Vikas Gautam
    Excel-buzz.blogspot.com

    Excel is not a matter of Experience, its a matter of Application.

    Say Thanks, Click * Add Reputation

  4. #4
    Registered User
    Join Date
    11-23-2010
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Subtotal with Countif

    Hi

    Thanks that's not returning the correct answer.

    B51:B124 is the range that is being filtered.

    I need to be able to filter and the formula to ignore the hidden cells.

  5. #5
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Subtotal with Countif

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

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

  6. #6
    Registered User
    Join Date
    11-23-2010
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Subtotal with Countif

    Neither works - still not returning the correct answer.... This is driving me crazy - been looking at it all day.

  7. #7
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Subtotal with Countif

    where is the filter? is it in b50 or b51
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  8. #8
    Registered User
    Join Date
    11-23-2010
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Subtotal with Countif

    The filter is on row B50 filtering data from B51:B124.

  9. #9
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Subtotal with Countif

    Try it like this...

    =SUMPRODUCT(SUBTOTAL(2,OFFSET(B51,ROW(B51:B124)-ROW(B51),0)),--(B51:B124>B40))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  10. #10
    Registered User
    Join Date
    11-23-2010
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Subtotal with Countif

    Yay!!!! we have a winner!!! it works! Thank you so much Tony :D :D :D

  11. #11
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Subtotal with Countif

    You're welcome. Thanks for the feedback!


    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

+ Reply to 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 & Subtotal
    By Burt_100 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-19-2014, 06:24 PM
  2. [SOLVED] Subtotal countif?
    By freud1 in forum Excel General
    Replies: 12
    Last Post: 10-02-2012, 07:31 AM
  3. [SOLVED] =Subtotal(countif,Range); Subtotal and countif in 1 formula [SOLVED]
    By thomas.mapua in forum Excel General
    Replies: 5
    Last Post: 01-06-2012, 11:33 AM
  4. Countif in subtotal
    By coolzero in forum Excel General
    Replies: 1
    Last Post: 05-25-2011, 03:41 PM
  5. > but < subtotal (3,...) or countif
    By PAR in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-22-2006, 05:55 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1