wouldn't that make it so both cells have to equal 1?

Yes, but that's how I interpreted this:

The way the second formula is written the M4
stats show day, nbc, and night completed because
it's seeing the night date in the secondary weapon
as completed.

What do the "--" mean before the "and" in your formula

AND will return either TRUE or FALSE. The "--" coerces those values to
either 1 for TRUE or 0 for FALSE.


--
Biff
Microsoft Excel MVP


"Bernie R." <Bernie wrote in message
...
I thought about changing "or" to "and", but wouldn't that make it so both
cells have to equal 1? Maybe I'm making this too difficult to see the
obvious answer.

I'll give "and" a try and see what happens. What do the "--" mean before
the "and" in your formula below?

"T. Valko" wrote:

Ok, I'm not sure what all your refeneces are to in this formula:

=IF((AND(($EV8=1),(OR(('TRAINING STATUS REPORT'!Z8=1),('TRAINING STATUS
REPORT'!AE8=1))))),1,0)

But, it sounds like you need AND rather than OR:

=IF(AND($EV8=1,TSR!Z8=1,TSR!AE8=1),1,0)

Or:

=--AND($EV8=1,TSR!Z8=1,TSR!AE8=1)



--
Biff
Microsoft Excel MVP


"Bernie R." <Bernie wrote in message
...
Great! That helps, but my real problem is with the second
formula...$EV8
would be the value from the formula you shortened below. example: a
soldier
has an M4 as a primary weapon and completed day, nbc, but not night; he
has a
M9 as a secondary weapon and completed day, nbc, and night. The way
the
second formula is written the M4 stats show day, nbc, and night
completed
because it's seeing the night date in the secondary weapon as
completed.

Thanks for your assistance.

"T. Valko" wrote:

You can reduce this:

=IF(OR(('TRAINING STATUS REPORT'!Y8="M4"),('TRAINING STATUS
REPORT'!Y8="M16A1"), ('TRAINING STATUS
REPORT'!Y8="M16A2-A4"),('TRAINING
STATUS REPORT'!Y8="M203"), ('TRAINING STATUS
REPORT'!AD8="M4"),('TRAINING
STATUS REPORT'!AD8="M16A1"), ('TRAINING STATUS
REPORT'!AD8="M16A2-A4"),
('TRAINING STATUS REPORT'!AD8="M203")),1,0)

To:

TRAINING STATUS REPORT = TSR

=IF(OR(TSR!Y8={"M4","M16A1","M16A2-A4","M203"},TSR!AD8={"M4","M16A1","M16A2-A4","M203"}),1,0)


--
Biff
Microsoft Excel MVP


"Bernie R." <Bernie wrote in message
...
I have a spreadsheet that tracks weapon type and their qualification
steps
in
two categories (primary and secondary weapon). There are 4 columns
in
each
category: weapon type, Day, NBC, Night.

I had this formula in the weapon type column:
=IF(OR(('TRAINING STATUS REPORT'!Y8="M4"),('TRAINING STATUS
REPORT'!Y8="M16A1"), ('TRAINING STATUS
REPORT'!Y8="M16A2-A4"),('TRAINING
STATUS REPORT'!Y8="M203"), ('TRAINING STATUS
REPORT'!AD8="M4"),('TRAINING
STATUS REPORT'!AD8="M16A1"), ('TRAINING STATUS
REPORT'!AD8="M16A2-A4"),
('TRAINING STATUS REPORT'!AD8="M203")),1,0)

I have this formula in the Day, NBC and Night pointing to the
respective
data:
=IF((AND(($EV8=1),(OR(('TRAINING STATUS REPORT'!Z8=1),('TRAINING
STATUS
REPORT'!AE8=1))))),1,0)

The problem I'm having is that if I have a M4 weapon in the primary
category
and there's a date in the Day and NBC columns, leaving Night
blank...and
the
person qualified on a secondary weapon completely. I'm not showing
the
blank
in the Night column for the M4 because the secondary weapons data is
showing
a date in all three of its columns.

I am using a "formula" worksheet to track the weapons qual data
worksheet,
and the "formula" worksheet feeds the summary quad worksheet.