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

 

[RobP]

Can I ask about your sample data. You provide an answer of 3, which suggests that Terry, John and Michaela all worked shifts < 24 hours. But John worked 24 hours. So for my understanding, I imagine the answer should be 2 only (ie. Terry and Michaela) ?

Also, another question, if Michaela you say has worked 12+8+8.5 (>24), then why she is included in the answer "3" also ? Same for Terry...

Sorry for my confusion with what you are asking.
Rob

 

[augustfan]

Can I ask about your sample data. You provide an answer of 3, which suggests that Terry, John and Michaela all worked shifts < 24 hours. But John worked 24 hours. So for my understanding, I imagine the answer should be 2 only (ie. Terry and Michaela) ?

Also, another question, if Michaela you say has worked 12+8+8.5 (>24), then why she is included in the answer "3" also ? Same for Terry...

Sorry for my confusion with what you are asking.
Rob

Thanks for your reply Bob

1) Yes, it should be 2 only, my bad.
2) for Michaela case, my goal is to count only once per person even if he/she "did more than once duty that lasted less than 24 hours", i.e. Michella 2.5 + 8.5 + 13 = count only as 1, the second and subsequent appearance doesnt matter anymore.

Thanks again and again!!!

 

[RobP]

Hi, thanks for clarifying.

So is this what you would need :

=COUNTA(UNIQUE((FILTER(A2:A8,B2:B8<24))))

Rgds
Rob

 

[augustfan]

Hi, thanks for clarifying.

So is this what you would need :

=COUNTA(UNIQUE((FILTER(A2:A8,B2:B8<24))))

Rgds
Rob

thank you sir, really thanks for your reply and consideration

but i am hopelessly using excel 2016 , anyway i am very grateful for your reply

 

[RobP]

ah, apologies.. I was convinced I'd seen O365 next to your name for some reason..

 

[shinigamilight]

Take out the unique values from Column A with the help of remove duplicates feature in the data tab

=SUMPRODUCT(--(COUNTIFS(B2:B8,"<24",A2:A8,I2:I4)>0))

 

[AhoyNC]

Another option without a helper column.
This is an array formula and needs to be entered with CTRL-SHIFT-Enter.

Book1
	A	B	C	D
1	Name	Duty hours		Less than 24 hrs
2	Terry	24		2
3	Terry	12
4	Michaela	12
5	Michaela	8
6	Michaela	8.5
7	John	24
8	Terry	6.5
Sheet1

Cell Formulas
Range	Formula
D2	D2	=SUM(IF(FREQUENCY(IF($B$2:$B$8<24,MATCH($A$2:$A$8,$A$2:$A$8,0)),ROW($A$2:$A$8)-ROW($A$2)+1),1))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[augustfan]

Another option without a helper column.
This is an array formula and needs to be entered with CTRL-SHIFT-Enter.

Book1
	A	B	C	D
1	Name	Duty hours		Less than 24 hrs
2	Terry	24		2
3	Terry	12
4	Michaela	12
5	Michaela	8
6	Michaela	8.5
7	John	24
8	Terry	6.5
Sheet1

Cell Formulas
Range	Formula
D2	D2	=SUM(IF(FREQUENCY(IF($B$2:$B$8<24,MATCH($A$2:$A$8,$A$2:$A$8,0)),ROW($A$2:$A$8)-ROW($A$2)+1),1))
Press CTRL+SHIFT+ENTER to enter array formulas.

WOW Ahoy, you nearly saved my day!!!!! I'd worked very well and exactly what I wanna achieve when the range is filled with data.

But when I try it in my excel with blank cells there is an error, if the range in the formula included a blank cell, the formula give "#N/A" as result, I wonder if its possible to work around this glitch?

I know i'd been given more than I should be asking for and thank you for your answer! I am already in the right direction.

I have another separate formula which worked well, ONLY when the duty hour is "24" and its like this:

=SUM(IF(24=P14:P48,1/(COUNTIFS(P14:P48,24,I14:I48,I14:I48)),0))

I tried many many methods and spent hours trying and I couldn't replace the variable "24" with "<24"

 

[AhoyNC]

This should work with blanks.
Again needs to be entered with CTRL-SHIFT-ENTER.

Book1
	A	B	C	D
1	Name	Duty hours		Less than 24 hrs
2	Terry	24		2
3		12
4	Michaela	12
5	Michaela	8
6	Michaela	8.5
7	John	24
8	Terry	6.5
Sheet1

Cell Formulas
Range	Formula
D2	D2	=SUM(IF(FREQUENCY(IF($A$2:$A$8<>"",IF($B$2:$B$8<24,MATCH($A$2:$A$8,$A$2:$A$8,0))),ROW($A$2:$A$8)-ROW($A$2)+1),1))