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.

 

[ryguy7272]

Sounds overly complicated. Read this:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

This should get you in the right direction. Also, have you considered using
a Pivot Table to do what you need to do?
http://www.contextures.com/xlPivot08.html

http://www.cpearson.com/excel/pivots.htm


Regards,
Ryan---

 

[Bernie R.]

I'll check it out. This spreadsheet was forced on us and I'm tracking 28
units. Just plugging the holes, making it work till this training is over.
I have no experience with Pivot tables. If I can transfer data quickly I
might consider transfering one unit to see how long it takes.

 

[T. Valko]

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)

 

[Bernie R.]

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]

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)

 

[Bernie R.]

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]

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.

 

[Bernie R.]

It seems to working. Hopefully this fix will last through the end of the
mobilization next month. I have to learn more about pivot tables and Access.

Thanks for all your help, really appreciate it.

 

[T. Valko]

You're welcome. Thanks for the feedback!