Prashant_91
New Member
- Joined
- Jun 1, 2021
- Messages
- 6
- Office Version
- 2016
- Platform
- Windows
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.
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.