Formulas

Hi Tara,

To simplify the explanation, let's refer to the first table as the parent table and the second table as the child table.

So what I would do is in the parent table, you add in a column (let's call it DayNumber) that indicates if the date is a weekday or weekend.

You could use a formula like =weekday(datefield, 2). What this will do is output a number 1 - 7 where 1 is Monday and 7 is Sunday.

You can then use an if statement along the lines of
DayType=if(DayNumber >=5, "Weekend", "Weekday").

This will categorise every day as either a Weekend (Fri, Sat, Sun) or Weekday (the other days).

Then in your child table, you have
Name Weekend Weekday (In A1 to C1)

You populate the name column with the 6 people (consider using the formula =UNIQUE(REFER TO THE NAME COLUMN IN THE PARENT TABLE))

Then in B2, you would write:
=countifs(ABSOLUTE REFERENCE TO THE NAME COLUMN IN PARENT TABLE, $A2, ABSOLUTE REFERENCE TO THE DayType COLUMN IN THE PARENT TABLE, B$1)

Drag that formula down and across and you should be there.

NOTE: The column headers in cells B1 and C1 are identical to the values in the DayType column so that the countif works where DayType = Weekday or Weekend respectively.