With almost universal availability of Internet and the availability of reliable on-line productivity tools, such as Google Docs, individuals as well as companies are switching over to Cloud Computing. In this blog I will post items of interest to my colleagues/patrons/clients.
I am trying to count the number of identical addresses/zip-codes that occur throughout their corresponding columns. I can do this through a simple COUNTIFS formula, but as the spreadsheet grows, I need it to build out on its own.
I have attempted a corresponding ArrayFormula, but I don't know what I need to add/change in order to get it to count properly. Any help would be appreciated!
=ARRAYFORMULA(IF(ROW(A:A)=1,"ARRAY - # of Events Here",IF(ISBLANK(B:B),"",COUNTIFS(C1:C,C1,D1:D,D1))))
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",))),)))