+ Reply to Thread
Results 1 to 4 of 4

Countif with Subtotal

  1. #1
    Registered User
    Join Date
    10-30-2014
    Location
    united states
    MS-Off Ver
    2010
    Posts
    11

    Countif with Subtotal

    I have a large table in which I used formulas to look up data on a different tab. The header row contains a filter so I can check/uncheck the data I want to see by month. I am trying to count the number of total comments entered each month. For example:

    Date Comment Order#
    January 1, 2017 Index/match formula returns a comment associated with this order # abc123
    February 4, 2017 index/match formula returns a comment associated with this order # cdf345
    February 25, 2017 index/match formula returns a comment associated with this order # def678
    March 5, 2017 index/match formula returns blank cell because there was no comment entered with this order # fgh789

    In using =COUNTIF(B2:B5000,"?*"), I get the right total number of comments (3). However, if I want to filter by only February, I'd like it to update to 2 since there were only 2 comments entered in that month. How can I create a formula that generates the subtotal of visible cells, but exclude anything with a formula that returns a blank cell?

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Countif with Subtotal

    Perhaps:

    =SUBTOTAL(103, B2:B5000)
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    10-30-2014
    Location
    united states
    MS-Off Ver
    2010
    Posts
    11

    Re: Countif with Subtotal

    Unfortunately, that still counts the blank cells with the formulas in it

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Countif with Subtotal

    I don't see how it could. You'd need to post a workbook with good sample data that demonstrates the problem you're describing in action.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

+ 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 and Countif
    By Lehany in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-10-2014, 01:53 PM
  3. [SOLVED] Subtotal countif?
    By freud1 in forum Excel General
    Replies: 12
    Last Post: 10-02-2012, 07:31 AM
  4. [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
  5. Countif in subtotal
    By coolzero in forum Excel General
    Replies: 1
    Last Post: 05-25-2011, 03:41 PM
  6. If, Countif, Subtotal...?
    By richandjo in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-24-2008, 06:44 AM
  7. countif and subtotal
    By suss-hmfc in forum Excel General
    Replies: 1
    Last Post: 01-18-2008, 10:10 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