Making formula too complicated?

[Bernie R.]

Here's the format of the columns/cells I'm working with:

col X Y Z AA AB
| Qualified | Day | NBC | Night | M203 |

In the Qualified column it should be either GO or NO GO
The other 4 columns are dates when the event occurs

I originally didn't have col AB and the formula was:
=IF($B12=""."".IF(Y12="NA","NA",IF(COUNT(Y12:AA12)=3,"GO","NO GO")
Col B is where the soldier's name is entered so if there no name Col X is
left blank.
I had no problems with this formula; however, I now added col AB and now
have to make a distinction between the two weapon systems.

In col I12 there's a selection of weapons (M4/16, M203, and M9).

The formula I had for col X was:

=IF($B12=""."".(Y12="NA","NA",IF(I12="M4/16",COUNT(Y12:AA12)=3,"GO","NO GO",
IF(I12="M203",COUNT(Y12:AB12)=4,"GO","NO GO")))

I get an error with the GO in the first "NO GO"

Any suggestions?

 

[Pete_UK]

I think you need a couple of ANDs, like so:

=IF($B12="","",IF(Y12="NA","NA",IF(AND(I12="M4/16",COUNT(Y12:AA12)=3),"GO",IF(AND(I12="M203",COUNT(Y12:AB12)=4),"GO","NO
GO"))))

I've also tidied up a bit.

Hope this helps.

Pete

 

[Bernie R.]

Worked great, Thanks! Everyone is super in this group. Appreciate all the
help.

 

[Pete_UK]

You're welcome, Bernie - thanks for feeding back.

Pete