yogi_Compute Row By Row Time Worked by Team (T) Or Self (S)

Google Spreadsheet   Post  #2137

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-23-2017

question by LiquidLight:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/S0YY7Rs2nRU;context-place=mydiscussions

How to convert a column specific formula to an array formula?

I'm trying to convert a column specific formula to an array formula. I've having some difficulty and hoping someone out there can help :)

Row 1 houses my headings (and a few other array formula I've converted from column specific formula) but I'm stumped by this one.

This formula currently has to sit in each cell in column O (except O1 which is the heading):

=if(AND(count(J2:N2)=1,E2=0),"S",if(AND(count(J2:N2)>1,E2=0),"T",if(AND(count(J2:N2)>0,E2>0),"T",)))

E are hours worked by me on a specific job (new job on a new row)

J:N are the hours worked by other staff on the same job - it doesn't matter who they are, J:N just allows 5 cells for up to 5 other staff to be included in the same job (it may be these are all empty, it may be that only one or two have values) 

O is where I want this array formula to return whether the other staff have worked solo "S" on the job or teamed "T" with each other or with me.

"S" will show when COUNT(J2:N2)=1 and E2=0

"T" when either COUNT(J2:N2)>1 and E2=0     or     COUNT(J2:N2)>1 and E2>0

The formula above does this nicely... until I try to make it an array formula with the heading "Solo or Teamed?"

I'm getting stumped by the COUNT function I think. I started with this:

=ArrayFormula( if(row($A:$A)=1,"Solo or Teamed",if( len($A:$A),if( AND(count($J:$N)=1,E:E=0),"S",if( AND(count($J:$N)>1,E:E=0),"T",if( AND(count($J:$N)>0,E:E>0),"T",))),)))

...and progressively I got more and more lost!

Any help would be warmly received.

Cheers