Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex Function?
Dearest Wizards,
Typical Excel work schedule here. Employees names in Col A, Dates of a 4 week period on Row 1. What I want: Row 2 to have the number of WORKING employees on for that day. Why this is becoming complicated: Below the schedule grid is another two tables. One table has codes that go in the schedule grid that indicate WORKING employees i.e. 1 for lead worker, T for working the table, FL for working the floor etc. These codes would be in a column, say from B100 to B199. The other table has codes for NON-WORKING employees i.e. v for vacation, m for meeting, FM for FMLA etc. These codes would be in a column, say from B250 to B349. Id like to avoid a countif+countif+countif situation because I anticipate adding other codes as the years go by. Isnt there a formula I can use in row 2 that will reference the working codes column that will give me a count of just the actual working employees for that day? TIA for your assistance. Sam Beardsley |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex Function?
If you want to count the occurrences of 1, T and FL use
=SUM(COUNTIF(B100:B350,{1;"T";"FL"})) -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email) "Metolius Dad" wrote in message ... Dearest Wizards, Typical Excel work schedule here. Employee's names in Col A, Dates of a 4 week period on Row 1. What I want: Row 2 to have the number of WORKING employees on for that day. Why this is becoming complicated: Below the schedule grid is another two tables. One table has codes that go in the schedule grid that indicate WORKING employees i.e. "1" for lead worker, "T" for working the table, "FL" for working the floor etc. These codes would be in a column, say from B100 to B199. The other table has codes for NON-WORKING employees i.e. "v" for vacation, "m" for meeting, "FM" for FMLA etc. These codes would be in a column, say from B250 to B349. I'd like to avoid a countif+countif+countif situation because I anticipate adding other codes as the years go by. Isn't there a formula I can use in row 2 that will reference the 'working codes' column that will give me a count of just the actual working employees for that day? TIA for your assistance. Sam Beardsley |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex Function?
Peo,
Thank you for your reply. It's not quite what I wanted but I can make it work. A problem I'm having with your formula though is that it counts lowercase 't' and lowercase 'fl' even though the formula I entered is upper. Any fix to make it count only what's in the quotes? Thanks again, Sam "Peo Sjoblom" wrote: If you want to count the occurrences of 1, T and FL use =SUM(COUNTIF(B100:B350,{1;"T";"FL"})) -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email) "Metolius Dad" wrote in message ... Dearest Wizards, Typical Excel work schedule here. Employee's names in Col A, Dates of a 4 week period on Row 1. What I want: Row 2 to have the number of WORKING employees on for that day. Why this is becoming complicated: Below the schedule grid is another two tables. One table has codes that go in the schedule grid that indicate WORKING employees i.e. "1" for lead worker, "T" for working the table, "FL" for working the floor etc. These codes would be in a column, say from B100 to B199. The other table has codes for NON-WORKING employees i.e. "v" for vacation, "m" for meeting, "FM" for FMLA etc. These codes would be in a column, say from B250 to B349. I'd like to avoid a countif+countif+countif situation because I anticipate adding other codes as the years go by. Isn't there a formula I can use in row 2 that will reference the 'working codes' column that will give me a count of just the actual working employees for that day? TIA for your assistance. Sam Beardsley |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need some comments on my Utility_Move class module. | Excel Worksheet Functions | |||
building complex functions using the function icon in the dialog b | Excel Discussion (Misc queries) | |||
Inserting a string seach within a complex function | Excel Worksheet Functions | |||
Date & Time | New Users to Excel | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |