Counting totals between multiple columns for like items

[Bernie R.]

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.

 

[Bernie R.]

Sorry, thought I posted in the Excel group. Too many applications open.