Advanced counting? or do I need VBA for this?

[wigarth]

[TABLE="width: 1000"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD]week1
[/TD]
[TD]week2
[/TD]
[TD]week3
[/TD]
[TD]week4
[/TD]
[TD]week5
[/TD]
[TD]sum "ok" weeks
[/TD]
[TD]longest coherent period with OK
[/TD]
[TD] coherent status with "OK" backwards as of week 5
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Becky
[/TD]
[TD]ok
[/TD]
[TD]ok
[/TD]
[TD]failed
[/TD]
[TD]ok
[/TD]
[TD]failed
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Sam
[/TD]
[TD]failed
[/TD]
[TD]failed
[/TD]
[TD]ok
[/TD]
[TD]ok
[/TD]
[TD]ok
[/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]John
[/TD]
[TD]failed

[/TD]
[TD]failed
[/TD]
[TD]failed
[/TD]
[TD]ok
[/TD]
[TD]ok
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Paula
[/TD]
[TD]failed
[/TD]
[TD]ok
[/TD]
[TD]ok
[/TD]
[TD]failed
[/TD]
[TD]failed
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]0
[/TD]
[/TR]
</tbody>[/TABLE]

This is a simplified table I am working on. Its the formulas in Column "H" and "I" that I am struggling with.

Goal of tableis to calculate which person has achieved goals each week. And to specify whohas done most weeks coherent “OK”


Column H: Goalis to count how many coherent weeks in a row a person has done “OK” at thehighest from week 1 to week 5… For Becky this is 2 (week 1 and week 2)

Column I: Goalhere is to count which person has the most coherent amount of weeks as of nowwith the result “OK” (So If we imagine being in week 5 now, we need to count coherentweeks with OK from week 5, then week 4, then week 3 etc…

I can't seem to get this sorted by myself. Anyone please suggest.

Best Regards:
Wigarth

 

[kensandy1]

You'll need a protected data sheet to get this to work if you want to do it without VBA.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD]week1[/TD]
[TD]week2[/TD]
[TD]week3[/TD]
[TD]week4[/TD]
[TD]week5[/TD]
[TD]week6[/TD]
[TD]week7[/TD]
[TD]week8[/TD]
[TD]week9[/TD]
[TD]week10[/TD]
[TD]week11[/TD]
[TD][/TD]
[TD]=MAX(B6:L6)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Becky[/TD]
[TD]ok[/TD]
[TD]ok[/TD]
[TD]failed[/TD]
[TD]ok[/TD]
[TD]failed[/TD]
[TD]ok[/TD]
[TD]ok[/TD]
[TD]ok[/TD]
[TD]ok[/TD]
[TD]ok[/TD]
[TD]ok[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Sam[/TD]
[TD]failed[/TD]
[TD]failed[/TD]
[TD]ok[/TD]
[TD]ok[/TD]
[TD]ok[/TD]
[TD]ok[/TD]
[TD]ok[/TD]
[TD]ok[/TD]
[TD]ok[/TD]
[TD]failed[/TD]
[TD]ok[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]John[/TD]
[TD]failed[/TD]
[TD]failed[/TD]
[TD]failed[/TD]
[TD]ok[/TD]
[TD]ok[/TD]
[TD]failed[/TD]
[TD]failed[/TD]
[TD]ok[/TD]
[TD]ok[/TD]
[TD]ok[/TD]
[TD]ok[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Paula[/TD]
[TD]failed[/TD]
[TD]ok[/TD]
[TD]ok[/TD]
[TD]failed[/TD]
[TD]failed[/TD]
[TD]failed[/TD]
[TD]failed[/TD]
[TD]failed[/TD]
[TD]failed[/TD]
[TD]failed[/TD]
[TD]failed[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]=IF(B2="ok", IF(C2<>"failed",IF(B2=C2,IF(A6>1,A6+1,2),1), 0),0)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Drag the formula in B6 down and over
Drag the formula in N2 down.

That should give you what you want. Put the numbers from the If's into a data sheet and protect the formulas and this should work without VBA.

 

[wigarth]

First ofall, Thanks for the reply and thanks for the suggestion.
I reallyappreciate you taking the time to try and help.

Unfortunatelyit doesn’t work.

Becky comesout with 6 witch is correct, but Sam comes out with 2,4???
It was alot of “IF” in that formula. It seems like one of them is triggering wrong atsome point.

Best regards:
Wigarth

 

[wigarth]

Sorry! When pasting formula i missed one "," that should have been a ";" So when I fixed that it worked. Thank you so much.