Counting Unique Names upon fullfilling Another Condition at Another Cell

[augustfan]

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.

I am sorry I cant install the XL2BB on my company computer.

Thank you for your help in advance!!

Cheers

Also asked here Counting Number of Unique Names upon fullfilling Another Condition at Another Cell

 

[augustfan]

Hi bros, it'd been a while, Thank you very much for your answers!!! You guys are super helpful.

Just a final update, I eventually used a silly method to do the magic:

I added a row at the end of sheet and input a formula which will show a "A" if the value is greater than 0 and less than 24 (=IF(AND(Q14>0, Q14<24), "A", "")

Then at my target cell I used the following formula:
{=SUM(IF("A"=$Y$14:$Y$68,1/(COUNTIFS($Y$14:$Y$68,"A",$J$14:$J$68,$J$14:$J$68)),0))}
I don't really understand the logic but they did the trick, so...... everything is great!

Thanks guys you guys are wonderful!

 

[augustfan]

Hi bros, it'd been a while, Thank you very much for your answers!!! You guys are super helpful.

Just a final update, I eventually used a silly method to do the magic:

I added a row at the end of sheet and input a formula which will show a "A" if the value is greater than 0 and less than 24 (=IF(AND(Q14>0, Q14<24), "A", "")

Then at my target cell I used the following formula:
{=SUM(IF("A"=$Y$14:$Y$68,1/(COUNTIFS($Y$14:$Y$68,"A",$J$14:$J$68,$J$14:$J$68)),0))}
I don't really understand the logic but they did the trick, so...... everything is great!

Thanks guys you guys are wonderful!

Paragraph 3 should read as "I added a column"