Counting totals between multiple columns for like items
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.
|