+ Reply to Thread
Results 1 to 3 of 3

Counting Number of Unique Names upon fullfilling Another Condition at Another Cell

  1. #1
    Registered User
    Join Date
    11-24-2022
    Location
    Hong Kong
    MS-Off Ver
    2016
    Posts
    1

    Lightbulb Counting Number of Unique Names upon fullfilling Another Condition at Another Cell

    Hi, I'd a question that bothered me a few weeks. My worksheet is like this:

    Column A: Names of employees
    Column B: Duty hours of that particular shift

    What I want to achieve is: count the number of person, without duplicate, that worked duty shifts for less than 24 hours.
    E.g. Michela worked 12 hrs, 8 hrs and 8.5 hour shifts, although all of the shifts are less 24 hours, but I just want to count them as 1 person, and extending to the column of data.

    I'd also uploaded a sample of the sheet, which it showed "3 different person worked duty shifts less than 24 hours". Hope it helps in understanding.


    Thank you for your help in advance!!

    Cheers
    Attached Files Attached Files

  2. #2
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,407

    Re: Counting Number of Unique Names upon fullfilling Another Condition at Another Cell

    it showed "3 different person worked duty shifts less than 24 hours
    I see in your sheet only 2 persons with shifts less than 24 hours: Terry and Michela.

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

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

    Re: Counting Number of Unique Names upon fullfilling Another Condition at Another Cell

    Another option:

    =SUM((--((FREQUENCY((IF(B2:B8<24,MATCH(A2:A8,A2:A8,0))),ROW(A2:A8)-ROW(A2)))>0)))

    Confirmed with CTRL_SHIFT+ENTER

+ 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. Counting The Number Of Cell That Fit The Condition (E.g. Odd)
    By ExcelBeats in forum Excel Formulas & Functions
    Replies: 26
    Last Post: 03-19-2021, 07:51 AM
  2. counting unique value with multiple condition
    By Very Adisetyawan in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-14-2016, 07:02 AM
  3. Counting unique items only when a condition has been met
    By MeisterMan in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-28-2012, 12:36 PM
  4. Counting unique entries with if condition
    By Jigz in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 06-20-2012, 01:37 PM
  5. Counting Names based on a condition
    By sshishirkumar in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-29-2012, 03:49 AM
  6. Counting Unique Names
    By fieroetnl in forum Excel General
    Replies: 1
    Last Post: 10-13-2011, 07:01 PM
  7. Counting unique names with three column condition
    By moyounis in forum Excel General
    Replies: 9
    Last Post: 07-11-2011, 08:36 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