finding specific day in range

[DK]

I am OK with some Adv Beg excel techniques. But I am not sure how to get the
next step done.
I do a monthly schedule in grid format in excel, names down the left
(A-column), and Dates across the top(row-2).
I have set up a template to auto fill the date each time I enter the new
date for the month in the cell A1
range "DAY" is C2 to AG2. This starts at C2 with "=A1" then in C3 "=(c2+1)".
This is formated to show text days of the week "ddd". In the lower rows for
each person a letter is used to indicate where they are to work for this
day.
Some letters are for 12 hour shifts and some for 24hrs. This is easy, but I
need to use a letter on one site that has varying hours depending on the
day. For example "T" on a "Fri" equals 8 hours and "T" on "Sat", "Sun", or
"Mon" equals 12 hours. I have been using the COUNTIF function to determine
the number of each shift worked, but I need to distinguish between Fri and
other days. Is there a simple way to evaluate a persons range (row) and if
there is a "T" check to see if it is in a column that is a Friday or
saturday etc? Since this column changes monthly, I need the formula to
evaluate the date in the column that the "T" resides to see how to count it.

If this does not make sense I have the blank sheet and can send it to anyone
interested in helping.
I appreciate any help anyone can give me.
Thanks,
Mike
(e-mail address removed)

 

[Kevin Stecyk]

Mike,

I will send you a sample spreadsheet of what I think you are trying to
accomplish.

It involves using the weekday function to determine the day.

Kevin

 

[Mike]

You may want to make use of the WEEKDAY and SUMPRODUCT
functions.

The WEEKDAY function will require you to install the
Analysis ToolPak if not already done (simply from the menu
select Tools/Add Ins then check the Analysis TookPak
option)

The SUMPRODUCT function is very useful for counting the
number of occurances in a range based on two or more
conditions.

Combine these functions to count all the T's in row 3 if
the day in row 2 is a Friday (day=6).
=SUMPRODUCT((WEEKDAY(C2:AG2)=6)*(C3:AG3="T"))