| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with if and looping
Okay, I have a table that lists people, their age and then their work status.
Looks like this Name Age Day 1 Day 2 Day 3 Joe 18 Reg Stock Sick Jill 20 Leave OnCall Reg Mack 18 Reg OnCall Stock Now I have a roll up table that needs to show for each day how many 18 yrs olds worked. So it would look like this Age Day 1 Day 2 Day 3 18 2 2 1 20 1 1 1 I've tried a countif statement but there's too many variables. There's up to 20 status and about half are active and close to 50 ages. I've tried a macro but I keep getting stuck with looping through the days. How should I go about this? Help!!!! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with if and looping
You need to define your "worked" criteria. I can see that "sick" is not
worked. Is "oncall" worked??? -- Don Guillett Microsoft MVP Excel SalesAid Software "Rominall" wrote in message ... Okay, I have a table that lists people, their age and then their work status. Looks like this Name Age Day 1 Day 2 Day 3 Joe 18 Reg Stock Sick Jill 20 Leave OnCall Reg Mack 18 Reg OnCall Stock Now I have a roll up table that needs to show for each day how many 18 yrs olds worked. So it would look like this Age Day 1 Day 2 Day 3 18 2 2 1 20 1 1 1 I've tried a countif statement but there's too many variables. There's up to 20 status and about half are active and close to 50 ages. I've tried a macro but I keep getting stuck with looping through the days. How should I go about this? Help!!!! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with if and looping
Rominall,
Besides writing a macro you may use the subtotal method or a pivot table report to summarize the data? brotha lee "Rominall" wrote: Okay, I have a table that lists people, their age and then their work status. Looks like this Name Age Day 1 Day 2 Day 3 Joe 18 Reg Stock Sick Jill 20 Leave OnCall Reg Mack 18 Reg OnCall Stock Now I have a roll up table that needs to show for each day how many 18 yrs olds worked. So it would look like this Age Day 1 Day 2 Day 3 18 2 2 1 20 1 1 1 I've tried a countif statement but there's too many variables. There's up to 20 status and about half are active and close to 50 ages. I've tried a macro but I keep getting stuck with looping through the days. How should I go about this? Help!!!! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with if and looping
What about having a second sheet that has each status in one column, and the
next column has the "work state" in it. Then, on your rollup table, you can just use SUMPRODUCT formulae, looking up the "worked" vs. "not worked" from the reference column? HTH! -JT "Rominall" wrote: Okay, I have a table that lists people, their age and then their work status. Looks like this Name Age Day 1 Day 2 Day 3 Joe 18 Reg Stock Sick Jill 20 Leave OnCall Reg Mack 18 Reg OnCall Stock Now I have a roll up table that needs to show for each day how many 18 yrs olds worked. So it would look like this Age Day 1 Day 2 Day 3 18 2 2 1 20 1 1 1 I've tried a countif statement but there's too many variables. There's up to 20 status and about half are active and close to 50 ages. I've tried a macro but I keep getting stuck with looping through the days. How should I go about this? Help!!!! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with if and looping
it needs to be by age as well. How many 18 year olds worked on day 1 and how
many worked on day 2 etc. Maybe I've just been looking at it too long. It seems it should be pretty simple but I think it's fried my brain. "JimT" wrote: What about having a second sheet that has each status in one column, and the next column has the "work state" in it. Then, on your rollup table, you can just use SUMPRODUCT formulae, looking up the "worked" vs. "not worked" from the reference column? HTH! -JT "Rominall" wrote: Okay, I have a table that lists people, their age and then their work status. Looks like this Name Age Day 1 Day 2 Day 3 Joe 18 Reg Stock Sick Jill 20 Leave OnCall Reg Mack 18 Reg OnCall Stock Now I have a roll up table that needs to show for each day how many 18 yrs olds worked. So it would look like this Age Day 1 Day 2 Day 3 18 2 2 1 20 1 1 1 I've tried a countif statement but there's too many variables. There's up to 20 status and about half are active and close to 50 ages. I've tried a macro but I keep getting stuck with looping through the days. How should I go about this? Help!!!! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with if and looping
If desired, send your wb to my address below and I will take a look . Full explanation and before/after examples. -- Don Guillett Microsoft MVP Excel SalesAid Software "Rominall" wrote in message ... There are about 20 different status' and of those about 11 are working status'. I tried a pivot table, but I can't get it to work. I'm really lost here. I don't know which method is best or which one will work if I can get all the kinks out. "Don Guillett" wrote: You need to define your "worked" criteria. I can see that "sick" is not worked. Is "oncall" worked??? -- Don Guillett Microsoft MVP Excel SalesAid Software "Rominall" wrote in message ... Okay, I have a table that lists people, their age and then their work status. Looks like this Name Age Day 1 Day 2 Day 3 Joe 18 Reg Stock Sick Jill 20 Leave OnCall Reg Mack 18 Reg OnCall Stock Now I have a roll up table that needs to show for each day how many 18 yrs olds worked. So it would look like this Age Day 1 Day 2 Day 3 18 2 2 1 20 1 1 1 I've tried a countif statement but there's too many variables. There's up to 20 status and about half are active and close to 50 ages. I've tried a macro but I keep getting stuck with looping through the days. How should I go about this? Help!!!! |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with if and looping
On Tue, 16 Dec 2008 12:38:11 -0800, Rominall
wrote: Okay, I have a table that lists people, their age and then their work status. Looks like this Name Age Day 1 Day 2 Day 3 Joe 18 Reg Stock Sick Jill 20 Leave OnCall Reg Mack 18 Reg OnCall Stock Now I have a roll up table that needs to show for each day how many 18 yrs olds worked. So it would look like this Age Day 1 Day 2 Day 3 18 2 2 1 20 1 1 1 I've tried a countif statement but there's too many variables. There's up to 20 status and about half are active and close to 50 ages. I've tried a macro but I keep getting stuck with looping through the days. How should I go about this? Help!!!! Assume your names are in column A, ages in column B, work status for Day 1 in column C, for Day 2 in column D and so on. Assume your output table is in the same sheet, with the ages in column J, the number of working for day 1 in column K, for day 2 in column L and so on. Row 1 is used for the headings and the data is beginning from row 2. Then you may try the following formula in cell K2: =SUMPRODUCT(($B$2:$B$10=$J2)*(NOT(ISERROR(FIND("." &C$2:C$10&".",$Z$1))))) Change the 10 to fit the number of input data rows you have. Copy the formula to the right for as many days you have and copy it down for as many ages that you want to have in the summary. In cell Z1 you put a string containing all the different statuses that should be counted as "worked" separated with dots (.), like this ..Reg.Stock.Oncall. just add all other statuses that should be counted as worked and remember to have a dot at the beginning and at the end as well as between all statuses. Hope this helps / Lars-Åke |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with if and looping
Sure, you can include as many "search keys" as you like with SUMPRODUCT.
You're really only limited by to 1,024 character limit for a formula, but even that can be overcome if you really need it. "Rominall" wrote: it needs to be by age as well. How many 18 year olds worked on day 1 and how many worked on day 2 etc. Maybe I've just been looking at it too long. It seems it should be pretty simple but I think it's fried my brain. "JimT" wrote: What about having a second sheet that has each status in one column, and the next column has the "work state" in it. Then, on your rollup table, you can just use SUMPRODUCT formulae, looking up the "worked" vs. "not worked" from the reference column? HTH! -JT "Rominall" wrote: Okay, I have a table that lists people, their age and then their work status. Looks like this Name Age Day 1 Day 2 Day 3 Joe 18 Reg Stock Sick Jill 20 Leave OnCall Reg Mack 18 Reg OnCall Stock Now I have a roll up table that needs to show for each day how many 18 yrs olds worked. So it would look like this Age Day 1 Day 2 Day 3 18 2 2 1 20 1 1 1 I've tried a countif statement but there's too many variables. There's up to 20 status and about half are active and close to 50 ages. I've tried a macro but I keep getting stuck with looping through the days. How should I go about this? Help!!!! |
Linear Mode
