+ Reply to Thread
Results 1 to 4 of 4

Count number of filtered rows according to distinct values and another criteria

  1. #1
    Registered User
    Join Date
    10-05-2020
    Location
    Israel
    MS-Off Ver
    Office 2019
    Posts
    2

    Count number of filtered rows according to distinct values and another criteria

    Hello,
    I've trying to solve a formula that I'm stuck on for a few days.
    I have a table with the following 2 columns:
    Column D - Date
    Column J - Hours (Time format = 1:00)

    What I want to achieve is to show the number of rows in the table (Not including filtered or hidden rows) that the time in column J is bigger then 0:00 and for it to only count a certain date once.

    For example:

    1-Sep 1:00
    2-Sep 2:30
    Will count as 2

    1-Sep 1:00
    2-Sep 0:00
    Will count as 1

    1-Sep 1:00
    1-Sep 2:00
    Will count as 1

    If anyone has any idea how to solve this it would be much appreciated!
    Thanks!!
    Last edited by dino_ben; 10-05-2020 at 06:55 AM.

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Essex, UK
    MS-Off Ver
    various
    Posts
    2,115

    Re: Count number of filtered rows according to distinct values and another criteria

    it would help, I think, if you were to attach a sample file (refer banner at top of forum for 'how to' instructions)

    it might be that the below Frequency Array would work for you but, be warned, this is not very efficient (and volatile)

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    if you can store a visible flag in a spare column, e.g. =SUBTOTAL(2,D2) copied down, this would remove the volatile requirement (SUBTOTAL w/OFFSET) and simplify your calculation

  3. #3
    Registered User
    Join Date
    10-05-2020
    Location
    Israel
    MS-Off Ver
    Office 2019
    Posts
    2

    Re: Count number of filtered rows according to distinct values and another criteria

    I tried your formula but it didn't work for me.
    Attaching an example file.
    The calculation cell is at the total row at column D.

    Thanks!
    Attached Files Attached Files

  4. #4
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Essex, UK
    MS-Off Ver
    various
    Posts
    2,115

    Re: Count number of filtered rows according to distinct values and another criteria

    you have not quite copied correctly - in your formula, change

    ROW($D$1:$D$91)
    to
    ROW($D$2:$D$91)

    then re-confirm with CTRL + SHIFT + ENTER
    (if modified correctly you should get result of 18 - which is correct, I believe)

+ 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] Count Distinct Values with Multiple Criteria
    By stacey52891 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-25-2020, 01:19 PM
  2. Count distinct values based on two criteria
    By Manikandan Arumugam in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-16-2020, 09:09 AM
  3. 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
  4. Replies: 4
    Last Post: 11-01-2016, 03:28 PM
  5. Count distinct values that correspond to a criteria
    By Eduard in forum Excel Programming / VBA / Macros
    Replies: 23
    Last Post: 04-22-2013, 01:03 PM
  6. Replies: 1
    Last Post: 03-21-2012, 07:06 PM
  7. Count distinct values with criteria
    By greencardioid in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-29-2008, 06:48 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