1. COUNTIF problem

I have a number of staff roster sheets in and XL file.

The first left hand column has staff names listed down.
Row one has M, T, W, T, F, S, S repeated in cells for days of the week.

The rows below have ON, OFF etc to indicate staff presence or absence.

I need to count the number of weekend days that each staff member works. That is, I am looking for a way to count the number of occurences where the day of the week row is ="S" and the ON/OFF row is ="ON". (ie where an individual is rostered "ON" on Sat. or Sun.)

I have tried concatenating the two rows and done a COUNTIF for the concatenated cells(ie = SON). But this is messy!!!!

Is there a simpler way to write a conditional formula which searches and counts text strings in two rows? (ie without additional rows for concatenation or calcs). The roster is for 60 people over 12 months, so its needs to be as simple and compact as possible.

Ford Kristo

2. Re: COUNTIF problem

A B C D E F G H I J K L M N O
1 M T W T F S S M T W T F S S
2 Joe On On On Off Off On On Off On Off Off On Off Off
3 Jane Off Off Off On On Off Off On On On On Off On On

If that is correct, then you can use the following array formula to calculate the number of Saturdays and Sundays that Joe works:

<pre>=Sum((B1:O1 = "S") * (B2:O2 = "On"))
</pre>

That is an array formula, so you must hold down the Shift and Ctrl key when you press the Enter key to enter it in the cell. You will also have to adjust the ranges to fit the number of weeks in your sheet. Just copy that formula down a column to get the totals for the other employees.

3. Re: COUNTIF problem

I had a similar situation once and used a SUMIF approach.
I attach a worksheet to show how it worked

Instead of Using "ON" and "OFF" I used 1 and zero, which can be formatted to read "On"(1) or "Off"(0), using format type "ON;OFF;OFF".

Instead of Using "Mon" or "M" enter the first date,e.g. 12/02/2001(dd/mm/yyyy), which is a monday and be formatted to read "Mon": format type "DDD".

In a row above that use a row which evaluates the actual date to a day of the week by using Weekday(Date,2)

You can use SUMIF with the criteria being Day values graeter tha 5 for the weekend days.

It is easier to understand what I mean by looking at the attached sheet. By the way I made use of Conditional Formatting to highlight "ON" or "OFF", which has the benefit of showing up any cells which do not have either 1 or 0 (or blank).

Hope it makes sense and is of use to you.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•