+ Reply to Thread
Results 1 to 5 of 5

How to use countif with subtotal

  1. #1
    Registered User
    Join Date
    01-22-2020
    Location
    Southampton, England
    MS-Off Ver
    2010
    Posts
    5

    How to use countif with subtotal

    Hi,

    I am trying to count number of lines by department over 30 days old.
    Countif function works =COUNTIFS(C:C, "First Ops",D:D, ">29")

    However when I filter this by owner, eg. filter for "Dave" and "Brian", need the countif value to change also.

    Can anyone help me out

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,443

    Re: How to use countif with subtotal

    If you want conditional counting applied only to rows which pass through filters, so are visible, you need to add a column to your table. If your complete data were in A1:P1000 with column labels in row 1, and if col A would never be blank, so COUNTA(A2:A1000) = 999, add formulas.

    Q2: =SUBTOTAL(103,A2)

    Fill Q2 down into Q3:Q1000. The col Q formulas will return 1 when the row passes through the filter (visible) or 0 when it doesn't (hidden). Then use COUNTIFS formulas like

    =COUNTIFS(C$2:C$1000,"First Ops",D$2:D$1000,">29",Q$2:Q$1000,1)

  3. #3
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,685

    Re: How to use countif with subtotal

    In K7 Cell

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


    Drag it down


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,685

    Re: How to use countif with subtotal

    Missed to see your second condition which can be covered like this..

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

  5. #5
    Registered User
    Join Date
    01-22-2020
    Location
    Southampton, England
    MS-Off Ver
    2010
    Posts
    5

    Re: How to use countif with subtotal

    You're a hero this works like a dream

+ 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] How to do COUNTIF with SUBTOTAL
    By ZOZOZIAD in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 03-29-2018, 04:20 AM
  2. How to do COUNTIF with SUBTOTAL
    By ZOZOZIAD in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-28-2018, 09:02 AM
  3. [SOLVED] Subtotal with Countif
    By Goonygugu in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-25-2014, 09:15 AM
  4. Mix countif with subtotal
    By ExcelFailure in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-06-2014, 04:00 AM
  5. [SOLVED] Countif in Subtotal
    By lemuel in forum Excel General
    Replies: 3
    Last Post: 11-06-2012, 05:14 PM
  6. [SOLVED] Subtotal countif?
    By freud1 in forum Excel General
    Replies: 12
    Last Post: 10-02-2012, 07:31 AM
  7. [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

Tags for this Thread

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