+ Reply to Thread
Results 1 to 11 of 11

Subtotal with Countif

  1. 09-25-2014, 07:56 AM #1
    Goonygugu
    Goonygugu is offline
    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?
    Reply With QuoteRegister To Reply
  2. 09-25-2014, 08:06 AM #2
    duffry
    duffry is offline
    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.
    Reply With QuoteRegister To Reply
  3. 09-25-2014, 08:07 AM #3
    Vikas_Gautam
    Vikas_Gautam is offline
    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
    Reply With QuoteRegister To Reply
  4. 09-25-2014, 08:18 AM #4
    Goonygugu
    Goonygugu is offline
    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.
    Reply With QuoteRegister To Reply
  5. 09-25-2014, 08:45 AM #5
    Vikas_Gautam
    Vikas_Gautam is offline
    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.
    Reply With QuoteRegister To Reply
  6. 09-25-2014, 08:55 AM #6
    Goonygugu
    Goonygugu is offline
    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.
    Reply With QuoteRegister To Reply
  7. 09-25-2014, 09:03 AM #7
    martindwilson
    martindwilson is offline
    Forum Expert 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
    Reply With QuoteRegister To Reply
  8. 09-25-2014, 09:09 AM #8
    Goonygugu
    Goonygugu is offline
    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.
    Reply With QuoteRegister To Reply
  9. 09-25-2014, 09:10 AM #9
    Tony Valko
    Tony Valko is offline
    Forum Expert 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.
    Reply With QuoteRegister To Reply
  10. 09-25-2014, 09:12 AM #10
    Goonygugu
    Goonygugu is offline
    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
    Reply With QuoteRegister To Reply
  11. 09-25-2014, 09:15 AM #11
    Tony Valko
    Tony Valko is offline
    Forum Expert 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 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 & 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. [SOLVED] > 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1