Help with if and looping

[Rominall]

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!!!!

 

[Don Guillett]

You need to define your "worked" criteria. I can see that "sick" is not
worked. Is "oncall" worked???

 

[Brotha Lee]

Rominall,

Besides writing a macro you may use the subtotal method or a pivot table
report to summarize the data?

brotha lee

 

[Rominall]

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.

 

[JimT]

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]

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.

 

[Don Guillett]

If desired, send your wb to my address below and I will take a look . Full
explanation and before/after examples.

 

[Lars-Åke Aspelin]

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

 

[JimT]

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.