+ Reply to Thread
Results 1 to 9 of 9

SUBTOTAL and COUNTIFS combination

  1. #1
    Registered User
    Join Date
    10-29-2012
    Location
    Notts, England
    MS-Off Ver
    365 PRO
    Posts
    63

    SUBTOTAL and COUNTIFS combination

    Hi everyone, thanks for any help in advance.

    I want to use Subtotal because I plan on using filters, but I need to build a Countif/s into it somehow.

    I would like to count how many cells contain a numerical value between 9 and 10 in N3:N3000.

    Thanks

  2. #2
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: SUBTOTAL and COUNTIFS combination

    Look into SUMPRODUCT along with SUBTOTALS instead of COUNTIF/S to try and accomplish this.
    Spread the love, add to the Rep

    "None of us are as smart as all of us."

  3. #3
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: SUBTOTAL and COUNTIFS combination

    Hi.

    =SUMPRODUCT(SUBTOTAL(3,OFFSET(N3,ROW(N3:N3000)-MIN(ROW(N3:N3000)),)),0+(N3:N3000>=9),0+(N3:N3000<=10))

    Regards
    Click * below if this answer helped

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

  4. #4
    Registered User
    Join Date
    10-29-2012
    Location
    Notts, England
    MS-Off Ver
    365 PRO
    Posts
    63

    Re: SUBTOTAL and COUNTIFS combination

    Thanks XOR LX, works perfectly.

    In addition, how would you do the same but for one argument i.e. a count subtotal if N3:N3000 ="Text"

  5. #5
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: SUBTOTAL and COUNTIFS combination

    Just remove one of the two clauses (including the "0+" bit) and change the condition in the other (>=9 or <=10) to ="Text".

    Cheers

  6. #6
    Registered User
    Join Date
    10-29-2012
    Location
    Notts, England
    MS-Off Ver
    365 PRO
    Posts
    63

    Re: SUBTOTAL and COUNTIFS combination

    Got it now, I was leaving the 0 on the end.

    Thanks again.

  7. #7
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: SUBTOTAL and COUNTIFS combination

    You're welcome!

  8. #8
    Registered User
    Join Date
    09-09-2019
    Location
    Fortaleza
    MS-Off Ver
    2016
    Posts
    2
    Quote Originally Posted by XOR LX View Post
    Just remove one of the two clauses (including the "0+" bit) and change the condition in the other (>=9 or <=10) to ="Text".

    Cheers
    Hi!
    How about testing against a ondition in another column other than N?
    thanks!

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: SUBTOTAL and COUNTIFS combination

    Quote Originally Posted by santosonit View Post
    Hi!
    How about testing against a ondition in another column other than N?
    thanks!
    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ 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. Countifs with subtotal
    By eleaca in forum Excel General
    Replies: 5
    Last Post: 01-04-2015, 12:44 PM
  2. [SOLVED] COUNTIFS and SUBTOTAL Functions Help
    By kapeller in forum Excel General
    Replies: 8
    Last Post: 09-15-2014, 06:23 PM
  3. Subtotal and Countifs
    By pisgah in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-05-2013, 02:14 PM
  4. VBA: Using Evaluate / Countifs Combination to Compare Dates
    By vidyuthrajesh in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-08-2013, 02:02 PM
  5. Vba: Using Evaluate Sumproduct / Countifs combination for Date
    By vidyuthrajesh in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-29-2012, 07:44 PM

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