[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
dwarfer1
General User

Joined: 10 May 2011
Posts: 23

 Posted: Fri Mar 23, 2012 7:22 am    Post subject: (solved)assign number each time conditions are met column a1:a4 = shaun david roger kev column b1:b4 = shaun david 0 kev column c1:c4 = shaun david roger kev i want to assign a different number to each time all 4 names occur so a5 = 1 b5 = 0 c5=2 can this be done without writing lads of different formulasLast edited by dwarfer1 on Sat Apr 21, 2012 10:14 am; edited 1 time in total
ken johnson
Super User

Joined: 23 Apr 2009
Posts: 1851
Location: Sydney, Australia

Posted: Fri Mar 23, 2012 3:55 pm    Post subject:

I think you need to use a helper row.
Assuming the absence of a zero is enough to indicate that all four names are present, then...
 Code: =COUNTIF(A1:A4;0)=0
in A6 filled across to C6 will return TRUE, FALSE, TRUE in A6:C6.
Then this formula in A5 filled across to C5...
 Code: =IF(A6;SUM(\$A6:A6);0)
will return 1, 0, 2 in A5:C5.

Ken Johnson
_________________