+ Reply to Thread
Results 1 to 34 of 34

Countif when cells filtered

  1. #1
    Registered User
    Join Date
    11-20-2017
    Location
    Australia
    MS-Off Ver
    MS365
    Posts
    71

    Countif when cells filtered

    Hi
    Trying to get my countif formula to work when I filter certain cells. At the moment the countif is counting the entire spreadsheet against a criteria which is fine, but when I filter I want the total to reflect the visible cells and obviously once I clear the filter it will go back to the grand total

    Thanks

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,643

    Re: Countif when cells filtered

    Using SUBTOTAL:

    =SUBTOTAL(2,A1:A100)

    With A1:A100 is filtered range
    Quang PT

  3. #3
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,156

    Re: Countif when cells filtered

    welcome to the forum. you didnt explain how your COUNTIF formula looks like. do upload an excel sample so that we do not have to manually key in your data to do a testing.

    input the desired results so that we don't have to second-guess if what we are doing is correct or not. you may look at my signature to upload an eg that is easier to understand.

    the upload attachment must be done by going to Go Advanced. click on Manage Attachments. Choose file, upload and close the window.

    if i really guessed it wrongly, please upload something according to my recommendations. try to use the solution i have given and we'll help to advise what went wrong.

    if your formula looks like this:
    =COUNTIF(D8:D19,"abc")
    then maybe:
    =SUMPRODUCT(SUBTOTAL(3,OFFSET(D8,ROW(D8:D19)-ROW(D8),)),--(D8:D19="abc"))
    it's explained in the file of my link below*Tips & Tutorials I Compiled, sheet 10

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  4. #4
    Registered User
    Join Date
    11-20-2017
    Location
    Australia
    MS-Off Ver
    MS365
    Posts
    71

    Re: Countif when cells filtered

    Ok thanks
    So say my filtered range has a combination of 1 & letters, at the moment the subtotal 2 is only counting the 1's
    I need it to count certain letters

  5. #5
    Registered User
    Join Date
    11-20-2017
    Location
    Australia
    MS-Off Ver
    MS365
    Posts
    71

    Re: Countif when cells filtered

    OK thanks at the moment I have 3 formulas going
    =COUNTIF(AAB$7:AAB$212,"1")
    =COUNTIF(AAB$7:AAB$212,"N")
    =COUNTIF(AAB$7:AAB$212,"RR")+COUNTIF(AAB$7:AAB$212,"AL")+COUNTIF(AAB$7:AAB$212,"OS")+COUNTIF(AAB$7:AAB$212,"WC")
    They are working fine, but when I filtered my spreadsheet i want the total of visible cells only

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2502
    Posts
    13,700

    Re: Countif when cells filtered

    Perhaps something like this?

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

  7. #7
    Registered User
    Join Date
    11-20-2017
    Location
    Australia
    MS-Off Ver
    MS365
    Posts
    71

    Re: Countif when cells filtered

    no that doesn't work sorry
    how about just try this one formula
    the cell range is from AAB7 to AAB212 and counting the letter N

    =COUNTIF(AAB$7:AAB$212,"N")

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2502
    Posts
    13,700

    Re: Countif when cells filtered

    OK. What are you filtering in/out?

    Perhaps it best if you upload an Excel sample file (not screen shots or pics ... save retyping data).

    If you are not familiar with how to do this:

    To attach a file to your post,
    • be sure to desensitize the data
    • click “Go Advanced” (next to Post Quick Reply – bottom right),
    • scroll down until you see “Manage Attachments”, click that,
    • click “Browse”.
    • select your file(s)
    • click “Upload”
    • click “Close window”
    • click “Submit Reply”

    The file name will appear at the bottom of your reply.
    Last edited by FlameRetired; 11-21-2017 at 01:15 AM.

  9. #9
    Registered User
    Join Date
    11-20-2017
    Location
    Australia
    MS-Off Ver
    MS365
    Posts
    71

    Re: Countif when cells filtered

    OK thanks
    so N means Nightshift
    and I have a few guys in different supervisor crews, so when I filter for N on a certain day I then narrow the filter more so by selecting 1 supervisor.

  10. #10
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Countif when cells filtered

    Hi all- You need to add a (hidden?) helper column with this formula in row 7 and copied down:
    Please Login or Register  to view this content.
    This shows 1 if the row is visible, 0 if it's hidden (or AAB is blank). Now just use this column as an additional criteria for your COUNTIFS. For example, assuming AAH is the helper column, this should work:
    Please Login or Register  to view this content.
    Last edited by leelnich; 11-21-2017 at 01:46 AM.
    Clicking the Add Reputation star below helpful posts is a great way to show your appreciation.
    Please mark your threads as SOLVED upon conclusion (Thread Tools above Post # 1). - Lee

  11. #11
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2502
    Posts
    13,700

    Re: Countif when cells filtered

    I will need to see a sample file so I can ask better questions.

  12. #12
    Valued Forum Contributor Root_'s Avatar
    Join Date
    07-29-2017
    Location
    _
    MS-Off Ver
    2010+
    Posts
    514

    Re: Countif when cells filtered

    Try using COUNTIFS with a helper column (say, column AAZ, which may be hidden):

    - put the following formula in cell AAZ7 and drag-copy it down to cell AAZ212:
    =AGGREGATE(3,5,AAB7)

    - the followig formula will count N's in visible cells only:
    =COUNTIFS(AAB$7:AAB$212,"N",AAZ$7:AAZ$212,1)
    Last edited by Root_; 11-21-2017 at 01:43 AM.

  13. #13
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Countif when cells filtered

    Quote Originally Posted by emmagizer View Post
    OK thanks at the moment I have 3 formulas going
    =COUNTIF(AAB$7:AAB$212,"1")
    =COUNTIF(AAB$7:AAB$212,"N")
    Please Login or Register  to view this content.
    They are working fine, but when I filtered my spreadsheet i want the total of visible cells only
    The longer formula can be written thusly:
    Please Login or Register  to view this content.
    ...again assuming AAH is the helper column (as in post #10).
    Last edited by leelnich; 11-21-2017 at 02:07 AM.

  14. #14
    Registered User
    Join Date
    11-20-2017
    Location
    Australia
    MS-Off Ver
    MS365
    Posts
    71

    Re: Countif when cells filtered

    ok that sort of does work however it might be easier to paste a example of what Im trying to achieve, its a roster. So I have continues dates running across the top of the sheet till end of next year.

    So when I filtered for N on todays date I get 8 which is correct (Example 1) but then i want to narrow the search down to by a certain supervisor, so I select supervisor 1. So I should get 3 employees on Night-shift working under Supervisor 1 (Example 2)

    Hopefully this makes sense

    Any help on this would be great appreciated
    Attached Images Attached Images
    Last edited by emmagizer; 11-21-2017 at 07:45 PM.

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,309

    Re: Countif when cells filtered

    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  16. #16
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2502
    Posts
    13,700

    Re: Countif when cells filtered

    emmagizer,

    Can you upload a sanitized version Excel file of that screenshot? It will be easier to demonstrate.

    If you are not familiar with how to do this:


    To attach a file to your post,
    • be sure to desensitize the data
    • click “Go Advanced” (next to Post Quick Reply – bottom right),
    • scroll down until you see “Manage Attachments”, click that,
    • click “Browse”.
    • select your file(s)
    • click “Upload”
    • click “Close window”
    • click “Submit Reply”

    The file name will appear at the bottom of your reply.

  17. #17
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,156

    Re: Countif when cells filtered

    @Dave:
    this is the 2nd time you asked for a sample file and the 3rd time we've asked if you counted my request. OP has not even bothered to explain why he/she is not uploading a sample file. it's awesome you guys have so much patience, but i think we're spoiling the OPs a little.

  18. #18
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2502
    Posts
    13,700

    Re: Countif when cells filtered

    @benishiryo,

    Good point. I asked in so many other threads I lose count.

  19. #19
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,309

    Re: Countif when cells filtered

    Emmagizer - if you are unwilling to provide a sample workbook, it is unlikely that you will get the help you require. Please do not ignore such requests by forum members.

  20. #20
    Registered User
    Join Date
    11-20-2017
    Location
    Australia
    MS-Off Ver
    MS365
    Posts
    71

    Re: Countif when cells filtered

    Hi Ali
    Sorry this is not much help, i don't have rows hiddened they are filtered.

  21. #21
    Registered User
    Join Date
    11-20-2017
    Location
    Australia
    MS-Off Ver
    MS365
    Posts
    71

    Re: Countif when cells filtered

    Hi
    I have uploaded example pictures on a previous post, not sure what else I should be posted. Im new to this

  22. #22
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,693

    Re: Countif when cells filtered

    See instructions in Post #16

  23. #23
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,309

    Re: Countif when cells filtered

    We need the Excel file (.xls or .xlsx), NOT a picture of it!!!

  24. #24
    Registered User
    Join Date
    11-20-2017
    Location
    Australia
    MS-Off Ver
    MS365
    Posts
    71

    Re: Countif when cells filtered

    ok I've removed the confidential info
    Attached Files Attached Files

  25. #25
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,309

    Re: Countif when cells filtered

    Quote Originally Posted by emmagizer View Post
    Hi Ali
    Sorry this is not much help, i don't have rows hiddened they are filtered.
    You have hidden them by filtering them (it is the same thing in Excel's eyes). The solution I have pointed you to will work, if you try it.

  26. #26
    Registered User
    Join Date
    11-20-2017
    Location
    Australia
    MS-Off Ver
    MS365
    Posts
    71

    Re: Countif when cells filtered

    sorry everyone, I've only just noticed all the additional comments on the 2nd page.

    I have just uploaded the excel document

  27. #27
    Registered User
    Join Date
    11-20-2017
    Location
    Australia
    MS-Off Ver
    MS365
    Posts
    71

    Re: Countif when cells filtered

    Hi AliGW
    Is there any chance you could amend the formula in the attachment and send back?, as the solution sent in this link https://exceljet.net/formula/count-v...-with-criteria
    I find a bit confusing

  28. #28
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,309

    Re: Countif when cells filtered

    Not for the next few hours, sorry - I am now at work. Somebody else might help, but if not, I'll do it for you this evening (UK time). However, I would prefer it if you had a go and posted your attempt here to troubleshoot.

  29. #29
    Registered User
    Join Date
    11-20-2017
    Location
    Australia
    MS-Off Ver
    MS365
    Posts
    71

    Re: Countif when cells filtered

    Hi

    I've tried this formula mention before https://exceljet.net/formula/count-v...-with-criteria
    and I just dont understand it for me to be able to use it in my sheet. I don't reallly get what offset means, and everytime I've tried different ways I keep getting formula error messages, any help on this would be greatly apprecaited
    Attached Files Attached Files

  30. #30
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Countif when cells filtered

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


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


    C107
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  31. #31
    Registered User
    Join Date
    11-20-2017
    Location
    Australia
    MS-Off Ver
    MS365
    Posts
    71

    Re: Countif when cells filtered

    AWESOME THANKYOU SO MUCH
    Now one more question and I should be done, if I have an employee who will be demobbing from the project. I would put a D in the box (refer too employee 71) but I need to not count that one on my total numbers onsite, cause obviously they have gone
    Attached Files Attached Files

  32. #32
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Countif when cells filtered

    just count the number of "D" and deduct from offsite

    formula in C107
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  33. #33
    Registered User
    Join Date
    11-20-2017
    Location
    Australia
    MS-Off Ver
    MS365
    Posts
    71

    Re: Countif when cells filtered

    Actually I think i got it sorted, are you able to check for me if its ok?
    row 108
    Attached Files Attached Files

  34. #34
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Countif when cells filtered

    your offsite will be wrong as it will still count "D"

    use the solution i gave in post #32 to circumvent that

+ 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. How to Countif filtered data
    By thaimic in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-07-2016, 08:12 AM
  2. Countif without duplicate in filtered cells
    By nccwhk in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-15-2016, 03:25 AM
  3. Countif except hidden (filtered) cells
    By Sgt. in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-20-2012, 06:59 AM
  4. Replies: 8
    Last Post: 07-14-2012, 10:22 AM
  5. [SOLVED] Countif on filtered rows
    By djcfisher in forum Excel General
    Replies: 4
    Last Post: 05-21-2012, 04:25 PM
  6. Help With Countif filtered list
    By alwilly45 in forum Excel General
    Replies: 1
    Last Post: 09-12-2011, 12:46 PM
  7. CountIf in Filtered Table
    By cityofnr in forum Excel General
    Replies: 4
    Last Post: 01-06-2011, 10:54 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