+ Reply to Thread
Results 1 to 6 of 6

I need to count nonblank values in a filtered list by two criteria. Please help!

  1. #1
    Registered User
    Join Date
    01-25-2021
    Location
    North Florida
    MS-Off Ver
    18.2008.12711.0
    Posts
    49

    I need to count nonblank values in a filtered list by two criteria. Please help!

    Noob in distress.

    I'm trying to take conditional counts on a filtered range. It's for a dynamic tool that others will be using, a table that updates values based on the filters applied to the variables.

    Just the ranges I'm working on right now are PROGRAM NAME (I2:I7238), ENTRY DATE (M2:M7382) and EXIT DATE (O2:O7382). I have dynamic breakdown table that should look like this when all data is unfiltered:

    PROGRAM #Clients Enrolled #Clients Graduated

    Program A_______ 1441_____ 1139
    Program B_______ 4510_____ 3025
    Program C_______ 1369_____ 926
    Program D_______ 31 _____ 23
    Not Registered____ 30 _____ 0
    TOTAL__________ 7381_____ 5113

    I hope that table is legible. Right now, Clients Entered is reading correctly both filtered and unfiltered by using =SUMPRODUCT((I2:I7382="PROGRAM A")*(SUBTOTAL(103,OFFSET(I2,ROW(I2:I7382)-MIN(ROW(I2:I7382)),0)))) in the Clients Enrolled cells. The total Completed count cell uses =SUBTOTAL(102,O2:O7382). Now what I need is a formula that will give me the count of nonblank cells in O2:O7382 per program. Maybe it's something simple but I can't figure out how to get that column to return counts when Column I equals a specific value AND when Column O is nonblank for just the visible rows.

    Any help is greatly appreciated, especially if you can answer within the next hour. Thanks.
    Attached Files Attached Files
    Last edited by Jarvis_Cain; 01-27-2021 at 06:02 PM. Reason: Adding a data example

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: I need to count nonblank values in a filtered list by two criteria. Please help!

    Hi,
    Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

  3. #3
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: I need to count nonblank values in a filtered list by two criteria. Please help!

    Intuitively I would say
    =COUNTIFS(I2:I7382,"ENTER CRITERIA",O2:O7382,"<>")

    But it's a guess without seeing your file.

  4. #4
    Registered User
    Join Date
    01-25-2021
    Location
    North Florida
    MS-Off Ver
    18.2008.12711.0
    Posts
    49

    Re: I need to count nonblank values in a filtered list by two criteria. Please help!

    How about if you needed that COUNTIFS formula to only show the filtered rows?

  5. #5
    Registered User
    Join Date
    01-25-2021
    Location
    North Florida
    MS-Off Ver
    18.2008.12711.0
    Posts
    49

    Re: I need to count nonblank values in a filtered list by two criteria. Please help!

    I still need help with this. I've uploaded an Excel example with explanations.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: I need to count nonblank values in a filtered list by two criteria. Please help!

    This proposal employs a helper column (lets say E) which may be moved and/or hidden for aesthetic purposes.
    1. Paste the following into cell E2 and then double click the fill handle to copy down to E19:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    2. Paste the following into cell G27, drag the fill handle down to cell G30 and then, while are G27:G30 still selected, drag the fill handle across to cell H30:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Replies: 3
    Last Post: 10-05-2020, 06:29 AM
  2. Count Distinct Values With A Criteria In A Filtered List
    By JonnyBoy333 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-31-2019, 02:52 AM
  3. [SOLVED] Count Values in Filtered List / Return Final Row in a Filtered List
    By HarryGreenwood in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-22-2018, 06:52 AM
  4. [SOLVED] Extract a List of Values Filtered by Criteria
    By mcmahobt in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-16-2014, 09:04 AM
  5. How to count unique values in filtered list?
    By Cayenne in forum Excel Charting & Pivots
    Replies: 14
    Last Post: 10-23-2014, 04:58 PM
  6. [SOLVED] Count nonblank cells with multiple criteria
    By Daniel in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-17-2005, 06:10 PM
  7. Counting the Values that Match Criteria in a Filtered List
    By ExcelTip in forum Tips and Tutorials
    Replies: 0
    Last Post: 08-28-2005, 10:38 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