Formula, calendar of duties

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

Hi all,

Right what I want to do seems simple but I may have
overcomplicated the whole thing!

The idea behind the worksheet is that I want a duty
roster, so I know who has worked what days. I will have
it layed out as a calender with two columns.
Column 1. Person on duty
Column 2. To be used if someone else does that duty

So what I need is a total number of duties each person
has done.

I began with =COUNTIF(C5,""). C5 being column 2

I then thought in the next column "IF D5=1 then copy C4"
(column 1) I could sum all those labled Andrew Smith.

Can anyone suggest a better way I am positive there is a
way or could you suggest a solution to the query for
column 1.

Many many thanks

Michael
 
Hi Michael
You can do this with one formula

=SUMPRODUCT((A1:A10="Andrew Smith")*(B1:B10="")

This will create two arrays of ones and zeros, then multiply the corresponding components, and add up the products

If Andrew smith appears in cell A2 through A6, and another name appears in B5 and B8, then this would do the following

(0,1,1,1,1,1,0,0,0,0) * (1,1,1,1,0,1,1,0,1,1) = (0+1+1+1+0+1+0+0+0+0) =

The two arrays (A1:A10, B1:B10) must be equal in size and you can't use a column reference (A:A, B:B)

Good Luck
Mark Graesse
(e-mail address removed)

----- Michael wrote: ----

Hi all

Right what I want to do seems simple but I may have
overcomplicated the whole thing

The idea behind the worksheet is that I want a duty
roster, so I know who has worked what days. I will have
it layed out as a calender with two columns.
Column 1. Person on duty
Column 2. To be used if someone else does that dut

So what I need is a total number of duties each person
has done

I began with =COUNTIF(C5,""). C5 being column 2

I then thought in the next column "IF D5=1 then copy C4"
(column 1) I could sum all those labled Andrew Smith

Can anyone suggest a better way I am positive there is a
way or could you suggest a solution to the query for
column 1

Many many thank

Michae
 
Michael

"C5 being column 2" ? Sorry, I'm not with this. Are you confusing cells
with rows and columns ?

Column A is cells A1, A2, ... An, ... A65536

Row 2 is cells A2, B2, ... IV2

So C4 could be referring to row 4 and be the first row in the range you are
interested in. C5 the second row.

Please can you clarify.

You could have something like: =COUNTIF(C4:H4,"Andrew Smith") or

=COUNTIF(C4:H4, A2) if "Andrew Smith" is in cell A2

Is that what you meant ?

Regards

Trevor
 
Back
Top