Formula to Count Number of Hours when Target is Hit (Target: 2700)

[Prashant_91]

Hello Friends,

I need help calculating number of hours when the member hit the target of 2700. Below excel show multiple examples where all members (abc1, abc2,..) hit the target of 2700 after certain hours of work and 2 members (abc2 & abc7) are not able to hit the target.
So Ideally formula should provide the number of hours members took to hit 2700 total. If member is not able to hit 2700, then it should return 0 as an value. Also formula should exclude the blank cells and only count the cell which contain values (please do not remove blank cells).

I tried below formula but it is not giving me desired result.
=COUNT(OFFSET(B5,0,0,1,MAX(IFS(V5<2700,0,SUMIF(OFFSET(B5,0,0,1,COLUMN(B5:U5)-COLUMN(B5)+1),">0")<"2700",COLUMN(B5:U5)+1))-COLUMN(B5)+1))

Please help me with the formula which can give me correct hours only if target of 2700 is hit and which also exclude blanks.

 

[Prashant_91]

In addition to above, below formula does give me desired correct result when target total is 2700 or above but it also count all the hours IF target total is less then 2700. If this can be modify in a way where it gives 0 when target total is less then 2700 than it would work.

=COUNT(OFFSET(B2,0,0,1,MAX(IF(SUMIF(OFFSET(B2,0,0,1,COLUMN(B2:U2)-COLUMN(B2)+1),">0")<=2700,COLUMN(B2:U2)+1,0))-COLUMN(B2)+1))

See below screenshot:

Apologies for multiples screenshots rather then miniexcel file.

 

[Peter_SSs]

below formula does give me desired correct result when target total is 2700 or above

Does it? I think it also gives an incorrect result when the total exactly equals 2700. Try changing O2 in that example from 375 to 374

Try this mix of the two approaches.

21 06 02.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1		12 AM	1 AM	2 AM	6 AM	7 AM	8 AM	9 AM	10 AM	11 AM	12 PM	1 PM	2 PM	3 PM	4 PM	5 PM	6 PM	7 PM	8 PM	9 PM	10 PM
2	abc1							277	366	464	388	51	379	401	375	397	258						8
3	abc2				192	262	531	361	124	235	407	22	169		396								0
4	abc3	211	287					159	161	77	258	325	137	145	137	331	252	181	40				14
Count (2)

Cell Formulas
Range	Formula
W2:W4	W2	=COUNT(B2:INDEX(B2:U2,MATCH(TRUE,SUMIF(OFFSET(B2,0,0,1,COLUMN(B2:U2)-COLUMN(B2)+1),">0")>=2700,0)))

 

[Marcelo Branco]

Maybe this regular formula
=COUNT(B2:INDEX(B2:U2,MATCH(TRUE,SUBTOTAL(9,OFFSET(B2,,,,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20}))>=2700,0)))
confirmed with just Enter

M.

 

[Prashant_91]

Thank you Peter and Marcelo. Both the above formula are working very well and gives the required output.
Thank you so so much for all the help

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.

 

[Marcelo Branco]

Thank you so so much for all the help

My pleasure

M.