Making formula too complicated?
 

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?

Making formula too complicated?
 

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",COUN T(Y12:AB12)=4),"GO","NO
GO"))))

I've also tidied up a bit.

Hope this helps.

Pete

On Mar 12, 6:58*pm, Bernie R. <Bernie
wrote:
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?

Making formula too complicated?
 

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

"Pete_UK" wrote:

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",COUN T(Y12:AB12)=4),"GO","NO
GO"))))

I've also tidied up a bit.

Hope this helps.

Pete

On Mar 12, 6:58 pm, Bernie R. <Bernie
wrote:
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?

Making formula too complicated?
 

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

Pete

On Mar 12, 7:34*pm, Bernie R. <Bernie
wrote:
Worked great, Thanks! * Everyone is super in this group. *Appreciate all the
help.



"Pete_UK" wrote:
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",COU NT(Y12:AB12)=4),"GO","NO
GO"))))

I've also tidied up a bit.

Hope this helps.

Pete

On Mar 12, 6:58 pm, Bernie R. <Bernie
wrote:
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?- Hide quoted text -

- Show quoted text -