Counta & Multiple Conditions
Posted by Tom Boyd on February 13, 2002 7:36 AM
I haven't been able to figure this one out.
A B C D E F G H I J
1 1 2 3 4 5 6 7 8 9
2 A x x x x x
3 B x x x x x x
4 C x x x x x x
The rows represent attendance at events 1-9. An X indicates attendance. I need a column with a "yes" or "no" for each row, indicating whether the attendance goal has been met.
The goal is complicated, so bear with me:
-Attendance at ONE of either event 1 or 2 is mandatory
-Only events 3,5,7&8 count toward the goal (the others are used for a different formula)
-1/2 of these 4 events must be attended
Thanks in advance!
-Tom
Posted by Tom Boyd on February 13, 2002 7:38 AM
The example didn't post right. I'll try again:
"-" indicates a blank cell.
Thanks again!
-Tom
Posted by Mark W. on February 13, 2002 7:55 AM
Enter the formula...
=IF(AND(COUNTA($B2:$C2),COUNTA($D2,$F2,$H2,$I2)>1),"Yes","No")
...in a cell of your choice on row 2 and copy
down.
Posted by Tom Boyd on February 13, 2002 9:13 AM
I had to add >1 after $C2) but it works great! Thanks!
-Tom
Posted by Mark W. on February 13, 2002 9:24 AM
Important! Check your logic again...
You said, "Attendance at ONE of either event 1 or 2 is mandatory".
If you change the 1st AND() argument to...
COUNTA($B2:$C2)>1
...you're making attendance at both events mandatory.
As it was COUNTA($B2:$C2) would return one
of {0,1,2}. AND(0) makes the conditon FALSE while
AND(1) or AND(2) would be TRUE.
