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