| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting totals between multiple columns for like items
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting totals between multiple columns for like items
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--- -- RyGuy "Bernie R." wrote: 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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting totals between multiple columns for like items
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. "ryguy7272" wrote: 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--- -- RyGuy "Bernie R." wrote: 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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting totals between multiple columns for like items
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting totals between multiple columns for like items
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting totals between multiple columns for like items
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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting totals between multiple columns for like items
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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting totals between multiple columns for like items
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" wrote: 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. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting totals between multiple columns for like items
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Bernie R." <Bernie wrote in message ... 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" wrote: 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Always display items AND Include hidden items in totals | Excel Discussion (Misc queries) | |||
| Totals f(x) text values in multiple columns and cells | Excel Discussion (Misc queries) | |||
| Counting Unique Items with Multiple Criteria | Excel Worksheet Functions | |||
| Counting Unique Items with Multiple Criteria | Excel Worksheet Functions | |||
| Counting Unique Items with Multiple Criteria | Excel Worksheet Functions | |||
Linear Mode
