MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.